T-SQL datetime rounded to nearest minute and nearest hours with using functions

by 
declare @dt datetime

set @dt="09-22-2007 15:07:38.850"

select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

will return

2007-09-22 15:07:00.000
2007-09-22 15:00:00.000

The above just truncates the seconds and minutes, producing the results asked for in the question. As @OMG Ponies pointed out, if you want to round up/down, then you can add half a minute or half an hour respectively, then truncate:

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)

and you’ll get:

2007-09-22 15:08:00.000
2007-09-22 15:00:00.000

Before the date data type was added in SQL Server 2008, I would use the above method to truncate the time portion from a datetime to get only the date. The idea is to determine the number of days between the datetime in question and a fixed point in time (0, which implicitly casts to 1900-01-01 00:00:00.000):

declare @days int
set @days = datediff(day, 0, @dt)

and then add that number of days to the fixed point in time, which gives you the original date with the time set to 00:00:00.000:

select dateadd(day, @days, 0)

or more succinctly:

select dateadd(day, datediff(day, 0, @dt), 0)

Using a different datepart (e.g. hour, mi) will work accordingly.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. CROSS JOIN vs INNER JOIN in SQL
  3. Unpivot with column name
  4. How to generate a range of numbers between two numbers?
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. SET versus SELECT when assigning variables?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Convert varchar into datetime in SQL Server
  10. Generate Dates between date ranges
  11. Select statement to find duplicates on certain fields
  12. How to print VARCHAR(MAX) using Print Statement?
  13. Check if a row exists, otherwise insert
  14. How to update Identity Column in SQL Server?
  15. Concatenate values based on ID
  16. Splitting delimited values in a SQL column into multiple rows
  17. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  18. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  19. TransactSQL to run another TransactSQL script
  20. Store multiple bit values in a single table column
  21. Stored procedure that exports data into csv files only exports to one file
  22. Which SQL query is faster? Filter on Join criteria or Where clause?
  23. SQL Server equivalent to MySQL enum data type?
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. SQL take just the numeric values from a varchar
  27. Cumulative Total in MS Sql server [duplicate]
  28. SQL Server SELECT INTO @variable?
  29. COALESCE Function in TSQL
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment