Extracting time from Datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting time from Datetime

Author

Topic

challapavan
Starting Member

8 Posts

Posted - 2007-08-28 : 11:44:59

Hi there i have a column with datetime field...

i want to extract only time field to the new column...

Start Date

8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:30
8/24/2007 11:00
8/24/2007 11:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 13:00
8/24/2007 14:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 17:00
8/24/2007 20:00

i want something like

StartDate Start time
8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:00
8/24/2007 10:30
8/24/2007 11:00
8/24/2007 11:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 12:00
8/24/2007 13:00
8/24/2007 14:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 15:00
8/24/2007 17:00
8/24/2007 20:00

can anyone plz find the solution for this one.

Thanks a lot!!!

pavan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-28 : 11:50:19

You can use the CONVERT function with the appropriate style.

But I don't recommend on splitting these though since you won't be using datetime data type anymore. It makes doing calculations harder since the data will now be varchar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 12:02:33

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-08-29 : 06:25:16

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Time+Only+Function

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-08-29 : 06:53:04

use some thing like this to get only time

select substring(convert(varchar(30),getdate(),120),11,6)

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-29 : 07:09:37

quote:
Originally posted by sunsanvin

use some thing like this to get only time

select substring(convert(varchar(30),getdate(),120),11,6)

Vinod
Even you learn 1%, Learn it with 100% confidence.

However, the actual answer relies on the answer to my question

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-08-29 : 07:13:35

yes Madhi
you are correct

Vinod
Even you learn 1%, Learn it with 100% confidence.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-08-29 : 08:35:21

None of the answers given here return a time, they are return strings. Try to work with raw, unformatted data in SQL as much as possible, and avoid converting values to strings since at that point it is not longer an actual date or time or datetime and you are relying on date/time formats that might vary from client to server and you might have conversion or parsing issues. keep it simple, work with data in correct types.

To retrieve just the decimal portion of a number like 10.25,what do you do? You subtract 10.00 from 10.25, resulting in 0.25. You use the same approach with getting a time portion of a datetime. Just subtract the Date at midnight from the datetime, and the result is just the time.

this expression:

dateAdd(dd,datediff(dd,0,yourdate),0)

will return the yourdate at midnight, so:

select yourdate - dateAdd(dd,datediff(dd,0,yourdate),0)

will return just the time of yourdate.

It is actually very useful to store times separate from dates; more here: http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

also, here's a bunch of useful datetime functions including TimeOnly():

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources