Wednesday, 11 April 2012

Convert full datetime to start of the day in T-SQL

Nice trick to convert full datetime to just year-month-day portion, resetting hours, minutes, seconds. Works in T-SQL, SQL Server

This statement:
SELECT GETDATE() AS FullDate, DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') AS StartOfTheDay

Produces this result:
FullDate StartOfTheDay

2012-04-11 13:51:15.570 2012-04-11 00:00:00.000

Note1: you can put any datetime instead of GETDATE()

Note2: DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') is the functional equivalent of DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

No comments:

Post a Comment