Removing Time From A Datetime Value

How much time would a DateTime have if a DateTime had no time?

Ok, forgive the bad pun. But the point remains is a valid one. How can we efficiently strip off the time portion of a DateTime value in SQL Server?

I’ve seen a several methods proffered on the Internet. Some are quite clever; others are not so clever. The latter includes converting the DateTime to a VarChar and using string manipulation techniques to loop off the time. That’s not going to perform well.

So what is the best way? Let’s find out.

Three Methods

I’m going to narrow down the field to only three methods for manageability. There may be other methods out there. If you know of one that’s not covered here and you think may be better, please let me know.

To test each case, I’ll employ the conversion 10,000,000 times to see which finishes the quickest. I’ll do this with the following code.

SET NOCOUNT ON
DECLARE @cnt INT = 1;
DECLARE @start DATETIME = GETDATE();
DECLARE @var DATETIME;

WHILE @cnt < 10000000
BEGIN
SET @var = [time removal option]
SET @cnt += 1;
END

SELECT @var;
PRINT CAST(DATEDIFF(millisecond, @start, GETDATE()) AS INT);

Cast To A Float And Back

First let’s look at a technique that casts the DateTime to a float, gets the floor of the resulting value, and then casts it back as a DateTime.

CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME);

This option finished in a 16,256 milliseconds.

Adding A Date

The next option uses the DATEDIFF function to calculate the number of days since the beginning of time and then uses the DATEADD function to convert it back to a DateTime value.

DATEADD(dd,0, DATEDIFF(dd,0, GETDATE()));

This completed in 15,593 milliseconds, slightly better then the first option.

Converting To A Char And Back

Finally, let’s consider a technique that converts the DateTime to a Char of a specified format and then casts it back to a DateTime.

CAST(CONVERT(CHAR(11), GETDATE(), 113) AS DATETIME

As you may have expected, the conversion to a Char significantly slowed the process down. In fact coming it at 32,698 milliseconds, it took over twice as long as the second method.

Say, Do You Have The Time?

From the three methods I’ve considered, the second method which included using a DATEDIFF to count the number of days since the beginning of time slightly edged out the first method. Both significantly beat converting the DateTime to a Char string.

So, do you have a favorite method? If so, use the test script I’ve posted about and see how it performs. Let me know how it does.

About these ads

23 Responses to Removing Time From A Datetime Value

  1. Awesome post title!

    I’ve been a fan of the DATEDIFF() method for a long time (and I suggest you try your loop with just DATEDIFF(), you don’t need the DATEADD() as long as you stick with pre-2008 datetime types). I came to that decision based on a very similar approach to yours – try various methods in very large loops, and see which comes back fastest. Difference between casting to float/int and DATEDIFF() is minimal, but would you rather have $1 million or $1.0001 million? :-)

  2. Joe says:

    By the way, if you need to calculate the end of the day you can do it by

    SELECT @end_date = DATEADD(MINUTE, -1, DATEADD(D, 1, CAST(FLOOR(CAST(@end_date AS float)) AS datetime)));

    This uses the first method described above.

  3. Never found much value in calculating the end of the day. I assume you are using this for BETWEEN @start_date AND @end_date? I would much rather use >= @start_date AND < DATEADD(DAY, 1, @start_date). This is compatible with both DATETIME and SMALLDATETIME. With your method, if the column is DATETIME, you have the potential of missing rows that occurred in the last minute. And it's less tidy; I'm all about tidy. :-)

  4. Daniel Kim says:

    am i missing something, or is this for T-SQL before 2008? not sure how this performs but this seems simpler:

    SELECT CAST(GETDATE() AS DATE)
    or
    SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

  5. Andrew says:

    Cast(@datetime as date)

    By far the easiest and fastest – plus SQL will still use indexes on datetime columns – it won’t if you use datediff

    • Joe Webb says:

      Thanks Andrew. Yes, columns wrapped up in a function typically cannot use index seeks.

      For the post I was considering T-SQL code for systems that do not have the Date datatype. Thanks for mentioning it.

    • Andrew, the code we’re talking about is typically when we’re checking if column is in some range. The datediff operations in these cases are almost always on variables or constants, not on columns, and won’t affect the usability of an index. I’ve shared some thoughts on date range queries in my “bad habits” series and one of the things I stress is to avoid performing these operations on the columns (e.g. WHERE CONVERT(DATE, column) = CONVERT(DATE, CURRENT_TIMESTAMP)):

      http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

      Just as a quick example, these will both use an index on column just fine:

      WHERE column >= CONVERT(DATE, CURRENT_TIMESTAMP)
      AND column = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP
      AND column < DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))

      However, the former won't work for you if you need to worry about deploying your code to SQL Server 2005, because it doesn't support the DATE data type.

  6. Pingback: Tweets that mention Removing Time From A Datetime Value « WebbTech Solutions -- Topsy.com

  7. Johan Bijnens - ALZDBA says:

    SSC member Lynn Pettis posted a little overview at http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

    It didn’t have the nice performance numbers, but is still a good ref.

    Thank you for these tests.

  8. Marc Brooks says:

    I’ve got a ton about practical uses of the DATEADD(DATEDIFF trick and proper date/time range comparisons here:

    http://bit.ly/71dvRP

  9. Joe Celko says:

    Hopefully T-SQL will get the CURRENT_DATE to go with the CURRENT_TIMESTAMP and we will not have to do implicit or explicit CAST ( CURRENT_TIMESTAMP AS DATE) in code.

    • Joe, why? We need more ways to say this?

      DECLARE @d DATE = SYSDATETIME();
      SELECT @d;
      GO
      DECLARE @d DATE = GETDATE();
      SELECT @d;
      GO
      DECLARE @d DATE = CURRENT_TIMESTAMP;
      SELECT @d;

      If it’s going to be used in a DATE data type, I don’t see why we need yet another system function that does what several already do.

      I can see maybe if you want to store a date without time in a SMALLDATETIME or DATETIME column, but why not just change it to DATE? If you are only going to chop the time off in *some* cases, then your method will require two different paths, one where CURRENT_DATE is used, and one where CURRENT_TIMESTAMP is used.

    • Joe Webb says:

      The Date datatype was a great addition to SQL, but I’m not sure that I see a lot of value in a CURRENT_DATE function. What use case are thinking about when you wish for it?

      • My guess is he just wants to say:

        SELECT CURRENT_DATE;

        And that comes back as a DATE type without having to perform any convert etc. Though I find very little value in that. For most use cases, if the destination (variable, column, etc.) is a DATE type, then the conversion is done for you.

        A slightly more useful use case:

        SELECT * FROM table WHERE date_column = CURRENT_DATE;

        If you use SYSGETDATE() or CURRENT_TIMESTAMP or SYSDATETIME(), obviously you’re not going to come up with the any rows. Another one that is bound to trip people up, because there is no implicit conversion (though I think there should be):

        DECLARE @d DATE = SYSDATETIME();
        IF @d = SYSDATETIME()
        BEGIN
        PRINT ‘they are the same';
        END

        I see Celko’s point to a small extent, but I think 99% of the SQL Server world is already well aware of the limitations that our assumptions about date/time data places on us. :-)

  10. Pingback: SQL Server Blogs and news for the week 21/01/11 | John Sansom - SQL Server DBA in the UK

  11. Pingback: A NULL Is Not Unknown « WebbTech Solutions

  12. Pingback: Writing Better Queries Presentation Materials « WebbTech Solutions

  13. Pingback: TSQL Tip O’ the Week – 8/16/11 – DateDiff « SQL Feather and Quill

  14. Manoj Bhatt says:

    This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it by visiting this url.

    http://mindstick.com/Articles/b08c8553-60a2-4f35-a58f-60c538c7d136/?%E2%80%98SELECT%E2%80%99%20Command%20with%20Date%20Method

    Its also helped me lot to complete my task.
    Thanks

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

Follow

Get every new post delivered to your Inbox.

Join 36 other followers