Trunc(sysdate) in SQL Server

Question

What is the equivalent of:

TRUNC(SYSDATE) 

...in SQL Server 2005?

Calfskin

The question is legitimate - the OP wants the equivalent of ORACLE functionality. – Rabbinical

Answer 1

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)

Rabbinical

I have to do all that just to get TRUNC()? – Bookrack

This works, but it would be more correct to switch the 2nd and 3rd arguments of DATEADD. The second argument is the increment, so you're adding 0 to the days returned by DATEDIFF. – Alage

Answer 2

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)

Photomicrograph

If you're on SQL Server 2008 or later, just use CONVERT(date,getdate()). Date data type doesn't store time info. – Aragonite

Answer 3

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.

Rani

Answer 4

I simply use :

CONVERT(date, GETDATE())

Adonai