Trunc Date in SQL Server

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. It is this third parameter we will use in order to customize the format of the date.

[cc lang=”sql”]
— Month first
SELECT CONVERT(varchar(12),GETDATE(), 101) — 06/29/2009
SELECT CONVERT(varchar(12),GETDATE(), 110) — 06-29-2009
SELECT CONVERT(varchar(12),GETDATE(), 100) — Jun 29 2009
SELECT CONVERT(varchar(12),GETDATE(), 107) — Jun 29, 2009

— Year first
SELECT CONVERT(varchar(12),GETDATE(), 102) — 2009.06.29
SELECT CONVERT(varchar(12),GETDATE(), 111) — 2009/06/29
SELECT CONVERT(varchar(12),GETDATE(), 112) — 20090629

— Day first
SELECT CONVERT(varchar(12),GETDATE(), 103) — 29/06/2009
SELECT CONVERT(varchar(12),GETDATE(), 105) — 29-06-2009
SELECT CONVERT(varchar(12),GETDATE(), 104) — 29.06.2009
SELECT CONVERT(varchar(12),GETDATE(), 106) — 29 Jun 2009

— Time only
SELECT CONVERT(varchar(12),GETDATE(), 108) — 07:26:16
SELECT CONVERT(varchar(12),GETDATE(), 114) — 07:27:11:203

— Date Only No Time (SQL 2008) [thank you John]
SELECT Cast(GetDate() AS date); — 08/12/2011
[/cc]

9 comments
Ash 25 Jun 2015 at 11:30 am

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.

prakash 24 Jun 2015 at 9:21 am

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?

fatih 08 Feb 2013 at 11:56 am

thanks! very useful functions…

JohnNeville 02 Aug 2011 at 8:23 am

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

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!!

fotis 25 Feb 2011 at 4:37 am

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

Featured Articles

 Site Author

  • Thanks for visiting!
css.php