Thursday, January 17, 2008

Getting Time part only from SQL DateTime Value

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.

9 comments:

Anonymous said...

Hi,
Nice blog. Must try this sql query. Keep up the good work, :)

Anonymous said...

i think this is better solution

CONVERT(VARCHAR,getdate(),108)

you ll get only time part of datetime variable

Jamie Snell said...

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.

Anonymous said...

hi, good site very much appreciatted

Arjuna said...

Thank you all for the appreciations done.

Anonymous said...

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))

Arjuna said...

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)

Anonymous said...

This saved my day.
Thank you.

Arjuna said...

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.