Trunc Date in SQL Server

The title and solution here aren’t quite accurate.
The last suggestion (CAST(GetDate() AS DATE) is the only one equivalent to TRUNC(sysdate) in Oralce. The others are equivalents for Oracle’s TO_CHAR() function.
In Oracle, TO_CHAR(sysdate, ‘format’), returns a string representing the date with whatever format you specifyso, TO_CHAR(ssysdate, ‘MM/DD/YYYY’) is equivalent to CONVERT(VARCHAR(12),GETDATE(), 101).

Also, TRUNC() also allows you to truncate to beginning of week, month, year, as well as hour, minute, nearest second etc, by provision of a second parameter which defaults to days. TRUNC(’05-NOV-2011′,’MM’) returns 01-NOV-2011 and if ‘MM’ were ‘YY’ returns 01-JAN-2011
The TRUNC() for a date always returns a date datatype too,which is where it differs from the various converts you’ve listed here, it’s more suited to sorting and comparison in a query with no implicit conversion against other date columns.

Hope this helps.

Chris 25 May 2017 at 7:12 pm

I was looking for trunc(date,’MM’) as well… I can probably construct it, but it’s a PITA.

Reply to this comment

difference between sysdate vs trunc sysdate

Jorge 29 Aug 2015 at 5:29 am

Firefox 3.5.3 Windows XPI’m really loinvg the theme/design of your weblog. Do you ever run into any internet browser compatibility issues? A couple of my blog audience have complained about my site not operating correctly in Explorer but looks great in Safari. Do you have any advice to help fix this issue?

Reply to this comment

thanks! very useful functions…

From SS2008 onwards you can use the ‘date’ datatype.

SELECT Cast(GetDate() AS date);
— works fine, as does

DECLARE @dateonly date = GetDate();
SELECT @dateonly;

I find the above much easier to read in code.

HTH

Derek Dieter 12 Aug 2011 at 10:25 pm

Thank you John,

I did know about the new date only feature but needed to update this article accordingly. Thanks, it is much easier to read.

Derek

Reply to this comment

Swati 29 Aug 2015 at 7:34 am

Opera 12.02 Windows 7Oh my goodness! Impressive aitcrle dude! Thank you so much, However I am experiencing troubles with your RSS. I don’t understand why I can’t subscribe to it. Is there anybody having identical RSS issues? Anyone who knows the answer will you kindly respond? Thanx!!

Reply to this comment

a diferent approach:

create FUNCTION [dbo].[TimeOnly] ( @DateTime DATETIME )
RETURNS DATETIME
AS BEGIN
RETURN DATEADD(day, -DATEDIFF(day, 0, @datetime), @datetime)
END
GO

CREATE FUNCTION [dbo].[DateOnly] ( @DateTime DATETIME )
RETURNS DATETIME
AS BEGIN
RETURN DATEADD(dd, 0, DATEDIFF(dd, 0, @DateTime))
END

GO