Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
Thursday, May 01, 2008
TSQL - How to get the date element of a datetime object
In SQL Server 2000 and 2005 there is no concept of a DATE data type, only a DATETIME. So, how do you get the DATE element only, i.e. with a 00:00 time element. One of my colleagues needed to do this, so after a bit of google searching he discovered the following.
-- Get today's date without the time element
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.
In SQL Server 2008 there is a DATE data type so this will no longer be an issue.
-- Get today's date without the time element
SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.
In SQL Server 2008 there is a DATE data type so this will no longer be an issue.
// posted by Jane @ 5:38 PM 
Comments:
