Menu
What is the equivalent of:
TRUNC(SYSDATE)
...in SQL Server 2005?
462
TRUNC(SYSDATE) removes the time element from the date. Any date that has been fed through the TRUNC function has a date of midnight. Consequently, TRUNC(SYSDATE) is always earlier than SYSDATE (unless you execute it on the very moment of midnight).
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.
DECLARE @d datetime = '2020-02-02 02:02:02.002'; SELECT 'Input', @d; SELECT 'Truncated', DATETRUNC(millisecond, @d); Here's the result set, which illustrates that the truncated date is the same as the stored date.
Recommended:
DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
This is another alternative, but it's risky because of casting to a FLOAT. It's also been demonstrated to not scale performance as well as the DATEADD/DATEDIFF approach.
CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
Another option is to use CONVERT (MSSQL 2008 and later) and either use an appropriate style or use a style that you can then SUBSTRING. I have no idea about the performance compared to the dateadd/datediff solution though.
e.g.
SELECT SUBSTRING(CONVERT(nvarchar(30), GETDATE(), 120), 1, 16)
Returns:
2012-01-03 15:30
Example using group that lists rows created per minute (presupposes a 'created' datetime column):
SELECT SUBSTRING(CONVERT(nvarchar(30), created, 120), 1, 16) as [minute]
, COUNT(1) as [per min]
FROM foo
GROUP BY SUBSTRING(CONVERT(nvarchar(30), created, 120), 1, 16)
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
