What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server 2008?
For example:
declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)
-----------------------
2009-05-28 00:00:00.000
Thanks
The snippet I found on the web when I had to do this was:
dateadd(dd,0, datediff(dd,0, YOURDATE))
e.g.
dateadd(dd,0, datediff(dd,0, getDate()))
There are two ways to do this:
The correct way:
dateadd(dd,0, datediff(dd,0, getDate()))
The fast way:
cast(floor(cast(getdate() as float)) as datetime)
The fast way works because datetimes are simply kept as 8-byte binary values. Cast them to float, floor them, and cast them back and the time portion is gone. It's all just bit shifting with no complicated logic and it's very fast.
However, it relies on an implementation detail that microsoft is free to change at any time, even in an automatic service update. It's also not very portable. The correct way uses documented functions that are guaranteed to work, but is somewhat slower.
In practice, it's very unlikely that the implementation will change any time soon, but it's still important to be aware of the danger if you choose to use it.
Update This has been getting some votes lately, and so I want to add to it that since I posted this I've seen some pretty solid evidence that Sql Server will optimize away the performance difference between "correct" way and the "fast" way, meaning you should now favor the former.
In either case, you want to write your queries to avoid the need to do this in the first place. It's very rare that you should do this work on the database.
In most places, the database is already your bottleneck. It's generally the server that's the most expensive to add hardware to for performance improvements and the hardest one to get those additions right (you have to balance disks with memory, for example). It's also the hardest to scale outward, both technically and from a business standpoint; it's much easier technically to add a web or application server than a database server and even if that were false you don't pay $20,000+ per server license for IIS.
The point of all this is do this work at the application level. The only time you should ever find yourself truncating a datetime on Sql Server is when you need to group by the day, and even then you should probably have an extra column that you maintain set up insert/update time or maintain in application logic.
And you can reuse your code without losing performance if you wrap it as an inline UDF:
For SQL Server 2008 only
CAST(@SomeDateTime AS Date)
Then cast it back to datetime if you want
CAST(CAST(@SomeDateTime AS Date) As datetime)
In SQl 2005 your trunc_date function could be written like this.
(1)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
CAST(FLOOR( CAST( @date AS FLOAT ) )AS DATETIME)
END
The first method is much much cleaner. It uses only 3 method calls including the final CAST() and performs no string concatenation, which is an automatic plus. Furthermore, there are no huge type casts here. If you can imagine that Date/Time stamps can be represented, then converting from dates to numbers and back to dates is a fairly easy process.
(2)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CONVERT(varchar, @date,112)
END
If you are concerned about microsoft's implementation of datetimes (2) or (3) might be ok.
(3)
CREATE FUNCTION trunc_date(@date DATETIME)
RETURNS DATETIME
AS
BEGIN
SELECT CAST((STR( YEAR( @date ) ) + '/' +STR( MONTH( @date ) ) + '/' +STR( DAY(@date ) )
) AS DATETIME
END
Third, the more verbose method. This requires breaking the date into its year, month, and day parts, putting them together in "yyyy/mm/dd" format, then casting that back to a date. This method involves 7 method calls including the final CAST(), not to mention string concatenation.
CONVERT(DATE, ) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)