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)
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.
ReplyDeletehttp://www.sqlhub.com/2009/04/list-of-all-available-datetime-format.html