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.
-- 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
Popular search terms:
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