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 (http://blog.SQLAuthority.com)

About these ads

23 thoughts on “SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2

  1. 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é

    Like

  2. Pingback: SQL SERVER – DATE and TIME in SQL Server 2008 Journey to SQL Authority with Pinal Dave

  3. 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

    Like

  4. 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

    Like

  5. 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.

    Like

  6. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

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

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

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

  9. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s