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

Question

In SQL server 2008  I would like to get datetime column rounded to nearest hour and nearest minute preferably with existing functions in 2008   For this column value 2007-09-22 15 07 38 850  the output will look like   2007-09-22 15 08 -- nearest minute 2007-09-22 15    -- nearest hour

User · Answer

Rounded  down as in your example  This will return a varchar value of the date   DECLARE  date As DateTime2 SET  date    2007-09-22 15 07 38 850   SELECT CONVERT VARCHAR 16    date  120  --2007-09-22 15 07 SELECT CONVERT VARCHAR 13    date  120  --2007-09-22 15

User · Answer

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

User · Answer

Select convert char 8   DATEADD MINUTE  DATEDIFF MINUTE  0  getdate   0   108  as Time  will round down seconds to 00

User · Answer

I realize this question is ancient and there is an accepted and an alternate answer   I also realize that my answer will only answer half of the question  but for anyone wanting to round to the nearest minute and still have a datetime compatible value using only a single function   CAST YourValueHere as smalldatetime     For hours or seconds  use Jeff Ogata s answer  the accepted answer  above

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008