Wednesday, February 10, 2010

Extract Just Time from DATETIME data type in SQL Server 2000, SQL Server 2005 and SQL Server 2008

In this post i would like to list out the various methods to extract the time from DATETIME data type in SQL Server. Depends on the version of the SQL Server you need to use different methods. In SQL Server 2008 you can use any of these methods but in SQL Server 2005 and SQL Server 2000 only method 1,2 and 3 will work.

DECLARE @DT DATETIME

SET @DT = GETDATE()
-- Method 1:
SELECT CONVERT(VARCHAR(30),@DT,114)
Output:------------------------------



22:31:43:047
(1 row(s) affected)

-- Method 2:
SELECT CAST(@DT AS TIME)
Output:----------------



22:31:43.0470000
(1 row(s) affected)

-- Method 3:
SELECT DATEADD(DAY, DATEDIFF(DAY, @dt, 0), @dt)
Output:-----------------------



1900-01-01 22:31:43.047
(1 row(s) affected)

-- Method 4:
SELECT @DT - CAST(FLOOR(CAST(@DT AS FLOAT)) AS DATETIME)
Output:
-----------------------

1900-01-01 22:31:43.047
(1 row(s) affected)

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

1 comment:

Ritesh Shah said...

DateTime techniques are really very needed whenever you working on any project. I had one small script which was showing almost all available datetime format in SQL Server which might be useful for the reader to get the list.

http://www.sqlhub.com/2009/04/list-of-all-available-datetime-format.html