What is the best way to truncate a date in SQL Server?

by

To round to the nearest whole day, there are three approaches in wide use. The first one uses datediff to find the number of days since the 0 datetime. The 0 datetime corresponds to the 1st of January, 1900. By adding the day difference to the start date, you’ve rounded to a whole day;

select dateadd(d, 0, datediff(d, 0, getdate()))

The second method is text based: it truncates the text description with varchar(10), leaving only the date part:

select convert(varchar(10),getdate(),111)

The third method uses the fact that a datetime is really a floating point representing the number of days since 1900. So by rounding it to a whole number, for example using floor, you get the start of the day:

select cast(floor(cast(getdate() as float)) as datetime)

To answer your second question, the start of the week is trickier. One way is to subtract the day-of-the-week:

select dateadd(dd, 1 - datepart(dw, getdate()), getdate())

This returns a time part too, so you’d have to combine it with one of the time-stripping methods to get to the first date. For example, with @start_of_day as a variable for readability:

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(dd, 1 - datepart(dw, @start_of_day), @start_of_day)

The start of the year, month, hour and minute still work with the “difference since 1900” approach:

select dateadd(yy, datediff(yy, 0, getdate()), 0)
select dateadd(m, datediff(m, 0, getdate()), 0)
select dateadd(hh, datediff(hh, 0, getdate()), 0)
select dateadd(mi, datediff(mi, 0, getdate()), 0)

Rounding by second requires a different approach, since the number of seconds since 0 gives an overflow. One way around that is using the start of the day, instead of 1900, as a reference date:

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(s, datediff(s, @start_of_day, getdate()), @start_of_day)

To round by 5 minutes, adjust the minute rounding method. Take the quotient of the minute difference, for example using /5*5:

select dateadd(mi, datediff(mi,0,getdate())/5*5, 0)

This works for quarters and half hours as well.

More Related Contents:

  1. Check if table exists in SQL Server
  2. How do I create a unique constraint that also allows nulls?
  3. How do I get list of all tables in a database using TSQL?
  4. Get first day of week in SQL Server
  5. What is the significance of 1/1/1753 in SQL Server?
  6. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  7. Escape a string in SQL Server so that it is safe to use in LIKE expression
  8. Insert Update trigger how to determine if insert or update
  9. SQL time difference between two dates result in hh:mm:ss
  10. TSQL Email Validation (without regex)
  11. Dynamic SQL results into temp table in SQL Stored procedure
  12. SQL Server Output Clause into a scalar variable
  13. Sequence as default value for a column
  14. Aggregate bitwise-OR in a subquery
  15. TABLOCK vs TABLOCKX
  16. How to convert number of minutes to hh:mm format in TSQL?
  17. OR Operator Short-circuit in SQL Server
  18. How do I select last 5 rows in a table without sorting?
  19. T-SQL incrementing counter based on change in a column value
  20. SELECT INTO a table variable in T-SQL
  21. T-SQL query to show table definition?
  22. How can I insert binary file data into a binary SQL field using a simple insert statement?
  23. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  24. Include missing months in Group By query
  25. comma delimited list as a single string, T-SQL [duplicate]
  26. SQL SERVER – Understanding how MIN(text) works
  27. Pivot multiple columns based on one column in SQL Server
  28. @@ERROR and/or TRY – CATCH
  29. INSERT INTO @TABLE EXEC @query with SQL Server 2000
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment