Trunc Date in SQL Server
-
Posted on July 4, 2010 by Derek Dieter
-
3
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]
- Comments (RSS)
- Trackback
- Permalink
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.
difference between sysdate vs trunc sysdate
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?
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
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
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!!
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