Trunc Date in SQL Server
-
Posted on July 4, 2010 by Derek Dieter
-
2
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.
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
- Comments (RSS)
- Trackback
- Permalink
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
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
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
