Floor a date in SQL server

by

The key is to use DATEADD and DATEDIFF along with the appropriate SQL timespan enumeration.

declare @datetime datetime;
set @datetime = getdate();
select @datetime;
select dateadd(year,datediff(year,0,@datetime),0);
select dateadd(month,datediff(month,0,@datetime),0);
select dateadd(day,datediff(day,0,@datetime),0);
select dateadd(hour,datediff(hour,0,@datetime),0);
select dateadd(minute,datediff(minute,0,@datetime),0);
select dateadd(second,datediff(second,'2000-01-01',@datetime),'2000-01-01');
select dateadd(week,datediff(week,0,@datetime),-1); --Beginning of week is Sunday
select dateadd(week,datediff(week,0,@datetime),0); --Beginning of week is Monday

Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor.

More Related Contents:

  1. How can I remove duplicate rows?
  2. SQL Server: Examples of PIVOTing String data
  3. How do I create a unique constraint that also allows nulls?
  4. Using T-SQL, return nth delimited element from a string
  5. How do I get list of all tables in a database using TSQL?
  6. Get first day of week in SQL Server
  7. What is the significance of 1/1/1753 in SQL Server?
  8. SELECT DISTINCT on one column
  9. Using a if condition in an insert SQL Server
  10. Altering a column: null to not null
  11. best way to convert and validate a date string
  12. Confused about UPDLOCK, HOLDLOCK
  13. TSQL Try / Catch within Transaction or vice versa?
  14. TSQL Email Validation (without regex)
  15. Dynamic SQL results into temp table in SQL Stored procedure
  16. SQL Server Output Clause into a scalar variable
  17. TSQL: Create a view that accesses multiple databases
  18. TABLOCK vs TABLOCKX
  19. How to convert number of minutes to hh:mm format in TSQL?
  20. How do I select last 5 rows in a table without sorting?
  21. How to change default language for SQL Server?
  22. T-SQL incrementing counter based on change in a column value
  23. SELECT INTO a table variable in T-SQL
  24. T-SQL query to show table definition?
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  27. Include missing months in Group By query
  28. Is there an “IF EXISTS” test for a linked server?
  29. ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException
  30. SQL Server stored procedure return code oddity

Leave a Comment