SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2

A year ago I wrote post about SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice where I have discussed two different methods of getting datepart from datetime.

Method 1:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

Method 2:
SELECT CONVERT(VARCHAR(10),GETDATE(),111)

I have summarized my post suggesting that either method works fine and I prefer to use Method 2. However, with additional tests and looking at SQL Server internals very carefully, I want to suggest that Method 1 is better in terms of performance. While running on GETDATE() both of the above functions are equally fast and efficient. However, when above query is ran on very large data table performance different is visible.

For testing purpose I have ran few queries where I am getting same result.

Run Following query 10 times with client statistics on ( Shift+Alter+S) in SSMS.

USE AdventureWorks
GO
SELECT DATEADD(d, 0, DATEDIFF(d, 0, StartDate))
FROM Production.WorkOrder
GO

Now run another query 10 times with client statistics on.

USE AdventureWorks
GO
SELECT CONVERT(VARCHAR(10),StartDate,111)
FROM Production.WorkOrder
GO

While looking at the average of statistics in it is clear that first query with DATEADD is much more efficient.

Let us compare both of the above script together and see the client statistics.

While comparing Numbers of Bytes sent across client and server as well client processing time, and total execution time in all case DATEADD is better. I suggest all my readers to use DATEADD when only DATEPART is required.

If you are using SQL Server 2008 it has new DATE datatype which does not have time part. I suggest to use DATE datatype or TIME datatype for users of SQL Server 2008. Read more SQL SERVER – 2008 – New DataTypes DATE and TIME.

If you want your date in any other format refer the UDF SQL SERVER – Get Date Time in Any Format – UDF – User Defined Functions.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Best Practices, SQL DateTime, SQL Function, SQL Scripts
Previous Post
SQL SERVER – Get Common Records From Two Tables Without Using Join
Next Post
SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

Related Posts

23 Comments. Leave new

  • Can you explain about switch and case statements
    i need help on them

    Reply
  • R A Valencourt
    June 10, 2009 1:05 am

    Hello,

    I’m wondering whether use of FLOOR would be equivalent to your Method 1, both in how it’s executed by SQL Server, and thus in performance?

    Method 1:
    SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

    FLOOR Method:
    CAST( FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME )

    René

    Reply
  • chandrakant Singh
    March 9, 2010 11:40 am

    I Want to Become a good sql developer can you suggest me how to start Or what are the steps

    Reply
  • i m storing the date and time in date_time field with same id like..

    1. aman 7/29/2010 12:59:02 PM
    1. aman 7/29/2010 1:05:31 PM
    1. aman 7/29/2010 1:07:43 PM

    how can i count the records using where condition
    i m trying the following query but no any result:-

    select Count(*) from tbname where date_time=’7/29/2010′

    ouput is
    0

    where m doing mistake. please assist me.
    thanks
    vik

    Reply
  • are you sure you using the correct date format in where condition.
    First run the select and see the format of date and then you can use date >= ’29-07-2010′.

    I am sure its the date format and your date should be in the format of dd-mm-yyyy instead of dd/mm/yyyy

    Reply
    • thanks for reply Ashish…

      your suggestion works for me but the problem is that after using date >= ’29-07-2010′ will also display the data who is greater than ’29-07-2010′ date but i need only which is equals to date=’29-07-2010′. well i am trying to display the daily transaction/history of the report. so i m still trying and hoping for solution. hope u understand my problem. :)

      thanks regards
      vik

      Reply
    • It should be in unambiguous format like YYYYMMDD. Refer this post for more informations

      Reply
  • thanks for reply.
    in that case just use in your where condition as
    where date >= ’29-07-2010′ and date < '30-07-2010'

    Reply
  • or you can use between operator as well:-

    like

    where date between ’29-07-2010 00:00′ and ’29-07-2010 23:59:59′

    Reply
    • You should always use unambiguous formats like YYYYMMDD format. The code you posted depends on the server’s date settings

      Reply
  • We can simply use CONVERT(DATE, GETDATE()) right? It gives the date part of the datetime variable.

    Reply
  • Grim Repair
    July 19, 2012 5:27 pm

    I’m puzzled as to how a “solution” that depends on the date being so-many-characters – can even be considered. What if the locality and date format changes? Converting the date to a string format and using a substring to “chop off” the time, is what we called a “HACK” when people took pride in their work and formal education wasn’t considered a terrible thing.

    Reply
  • Ankur Mundhra
    August 2, 2012 5:47 pm

    I was wondering if using char(10) instead of varchar(10) would make any difference?

    Reply
  • Tarun Mahajan
    July 22, 2013 10:40 am

    SELECT CONVERT(VARCHAR(10),GETDATE(),111)

    what is 111 in this query? Can you explain me.. I am not able to understand it.

    Reply
  • i want to generate report that last 7 days how many bill are payed
    using SQL server
    soon i want to generate report by date daily,weekly and monthly
    i am trying like this please help me
    select count(bills) from table_name where date_column < sysdatetime();
    but its result is total of all bils
    i want to generete total bil of last few days

    Reply
  • Hi,
    I want to convert a datetime column in sqlite which is of format yyyy-mm-dd hh:mm:ss to seconds converted to 00 eg: 2014-12-27 16:46:59 to 2014-12-27 16:46:00
    please help..

    Thanks

    Reply
  • Aditi Sarkar
    May 19, 2015 9:12 am

    Great Post!! It worked for me. Thank you.

    Reply

Leave a Reply

Menu
Exit mobile version