Trunc(sysdate) in SQL Server

What is the equivalent of:

TRUNC(SYSDATE)

...in SQL Server 2005?

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](

[To see links please register here]

) (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)

As of SQL Server 2022 CTP 2.1, DATETRUNC() is now supported which should line up well with the TRUNC() capability in Oracle. Documentation can be found [here][1].


[1]:

[To see links please register here]