piątek, 7 grudnia 2012

End of a day with T-SQL


How to get begin/end of a day with T-SQL?

  1. SELECT GETDATE() -- current date and time [2012-12-07 15:04:34.680]
  2. SELECT CAST(GETDATE() AS DATE) -- cropped to date part only [2012-12-07]
  3. SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME) -- back do DATETIME - begin of a day [2012-12-07 00:00:00.000]
  4. SELECT DATEADD(day,+1,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) -- begin of a next day [2012-12-08 00:00:00.000]
  5. SELECT DATEADD(ms,-3,DATEADD(day,+1,CAST(CAST(GETDATE() AS DATE) AS DATETIME))) -- end of a day [2012-12-07 23:59:59.997] -- Accurancy of DATETIME is rounded to increments of .000, .003, or .007 seconds

Solution above works with SQL Server 2008 and above as DATE datatype was introduced then.