Have you tried getting the time value only from a SQL DateTime value?
I have seen many people struggling with datetime fields in their programming life. Some people face problems getting the date only from datetime field. If you are one who is struggling please read my article on April 2007.
In this article I would like to mention how you can get the time only from a datetime value.
SELECT CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
Here what I am doing is simply casting the datetime value returned by GETDATE() into FLOAT then I am substracting the full value (value without the fractions) from that. So I will get the fraction part of the float value.
Note that when you cast a datetime value to a float, the full part represents the date and the fraction part represents the time.
2008-01-18 18:22:15.640
39463.7654587963
Then I will cast the result back to the datetime which brings me the time.
This method of casting datetime value to a float value is always handy when working with datetime values.
Subscribe to:
Post Comments (Atom)
-
If you are wondering a way to change the column header appearing in the .Net GridView control in run time then this post will help you to ge...
-
Last week I spent few hours to figure out why Samsung Galaxy S2 was not connecting to Samsung Kies through USB cable. I could browse phone c...
9 comments:
Hi,
Nice blog. Must try this sql query. Keep up the good work, :)
i think this is better solution
CONVERT(VARCHAR,getdate(),108)
you ll get only time part of datetime variable
This keeps it in a datetime format though, which is useful for sorting.. if you sorted
CONVERT(VARCHAR,getdate(),108)
11:00 PM would come before 2:00 PM
Great Tip.
hi, good site very much appreciatted
Thank you all for the appreciations done.
Be warned though, this example can introduce rounding errors.
Try this and see:
DECLARE @Time AS DATETIME
SET @Time = '2011-01-01 15:55:02.000'
SELECT CAST(CAST(@Time AS FLOAT) - FLOOR(CAST(@Time AS FLOAT)) AS DATETIME)
SELECT @Time - FLOOR(CAST(@Time AS FLOAT))
Thanks for pointing this out, to overcome the rounding issues as you pointed out following is fine,
SELECT @Time - FLOOR(CAST(@Time AS FLOAT))
To do in a more controlled way you can use the following methods as well.
SELECT CAST(ROUND(CAST(@Time AS FLOAT) - FLOOR(CAST(@Time AS FLOAT)), 10) AS DATETIME)
SELECT CAST(CAST(CAST(@Time AS FLOAT) - FLOOR(CAST(@Time AS FLOAT)) AS FLOAT(10)) AS DATETIME)
This saved my day.
Thank you.
To make things complete I thought to add 2 more ways you can get the time part from a DATETIME field.
SELECT CONVERT(NVARCHAR(8), GETDATE(), 108)
If using SQL 2008 or newer you can also use,
SELECT CAST(GETDATE() AS TIME)
Hope this helps.
Post a Comment