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)