Menu
How do i trunc the date in sql server 2008 like this :
I have 2012-01-02 12:04:11.443 and I want only to select 2012-01-02 12:00:00.000 and 2012-01-02 12:04:00.000 (hour and minute level)
212
SQL Server SPID – What is it? » In Oracle there is a function (trunc) used to remove the time portion of a date. In order to do this with SQL Server, you need to use the convert function. Convert takes 3 parameters, the datatype to convert to, the value to convert, and an optional parameter for the formatting style.
We can use DATEPART() function to get the HOUR part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as hour or hh.
If you want to TRUNC to the second, there are a few ways to do that. A couple of the simplest are using Date addition of 0 which will implicitly convert TIMESTAMP to a DATE, dropping the fractional seconds and then add 0 which, obviously, doesn't change the value. Then CAST that result back to a TIMESTAMP.
declare @D as datetime = '2012-01-02T12:04:11.443'
select dateadd(hour, datediff(hour, 0, @D), 0)
select dateadd(minute, datediff(minute, 0, @D), 0)
This seems to work:
select [Rounded Time] =
dateadd(mi,datediff(mi,0,dateadd(ss,30,a.DT)),0)
from
(
select dt = convert(datetime,'8:24:29.997')
union all
select dt = convert(datetime,'8:24:30.000')
) a
Results:
Rounded Time
------------------------------------------------------
1900-01-01 08:24:00.000
1900-01-01 08:25:00.000
(2 row(s) affected)
As of SQL Server 2022 CTP 2.1, there is a native function in SQL to do this called DATETRUNC(). You can pick the date/time level to which you would like to truncate the date. Documentation is here.
DATETRUNC(hour, YourDate)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
