Removing Time From A Datetime Value
January 13, 2011 23 Comments
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 DATETIMEAs 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.
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?
Thanks for the confirmation, Aaron. I’ll give that a try too.
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.
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.
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)
Ahh, good point. Yes I was specifically referring to instances where the Date datatype doesn’t exist – ala 2005 and before.
Yes, we don’t always have the luxury of writing T-SQL that only targets SQL 2008 and above.
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
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.
Pingback: Tweets that mention Removing Time From A Datetime Value « WebbTech Solutions -- Topsy.com
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.
Thanks for sharing, Johan!
I’ve got a ton about practical uses of the DATEADD(DATEDIFF trick and proper date/time range comparisons here:
http://bit.ly/71dvRP
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.
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.
Pingback: SQL Server Blogs and news for the week 21/01/11 | John Sansom - SQL Server DBA in the UK
Pingback: A NULL Is Not Unknown « WebbTech Solutions
Pingback: Writing Better Queries Presentation Materials « WebbTech Solutions
Pingback: TSQL Tip O’ the Week – 8/16/11 – DateDiff « SQL Feather and Quill
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