SQL Server: Extracting JUST Time From Datetime Value

SQL Server: Extracting JUST Time From Datetime Value

Posted by Brad Wood
Mar 26, 2009 08:18:00 UTC
Tonight I found myself needing to extract just the time from a datetime column in SQL Server 2000. After a bit of Googling, it became clear that there are about 1 million articles on how to extract just the date from a datetime, but not very many articles taking about just getting time. For reference, this is how I ended up doing it.In MS SQL, the convert function is very handy for dealing with dates. There are a number of different conversion styles it supports:
http://www.w3schools.com/sql/func_convert.asp
[code]SELECT CONVERT(VARCHAR,getdate(),108)[/code]
This tells SQL Server to convert the current date/time into a varchar using style 108, which is "hh:mm:ss". The most common conversion style I use is probably 101, or "mm/dd/yy". There are a couple of annoying things about convert. One is that is returns a string. This means that if you want to keep doing date manipulations you usually have to cast it back to a datetime one way or another. I guess technically ColdFusion's dateformat function returns a string, but CF (being typeless) is better as auto-casting those strings back into dates when it can. The other main annoyance is that we just have about 19 preset date/time formats to choose from. I guess ColdFusion spoils us with masks that give us full control.

 


Syed Tayyab Ali

Good stuff...

Drew

What causes the query to return '1901/01/01 21:38: 45.000' when it should only return '21:38: 45' ???

Brad Wood

@Drew: That will only happen if you cast the string that the convert function returns BACK to a datetime data type. I'm actually not sure why the year defaults to 1901 since I'm pretty sure SQL Server's epoch date is 1/1/1970. You will notice that SELECT cast('' as datetime) produces "1900-01-01 00:00: 00.000".

If you are on SQL Server 2008 you can use the new TIME data type.

urjo

Awesome information...just what I needed!

Purvi

It really helped me.

aparna

Good Stuff.

jareqp

I found this:

select dateadd(day, datediff(day, getdate(), 0), getdate());

There are more details and performance comparisons on: http://dbstandard.com/showthread.php?tid=1

happy

nice..................

Ajith

You can extract time alone using this simplest way in

Select CAST(DateColumn AS TIME) As SplitTime From Table

Site Updates

Entry Comments

Entries Search