SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extracting time from Datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

challapavan
Starting Member

India
8 Posts

Posted - 08/28/2007 :  11:44:59  Show Profile  Reply with Quote
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

USA
35954 Posts

Posted - 08/28/2007 :  11:50:19  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 08/28/2007 :  12:02:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/29/2007 :  06:25:16  Show Profile  Reply with Quote
See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Time+Only+Function
Go to Top of Page

sunsanvin
Flowing Fount of Yak Knowledge

India
1265 Posts

Posted - 08/29/2007 :  06:53:04  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 08/29/2007 :  07:09:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sunsanvin
Flowing Fount of Yak Knowledge

India
1265 Posts

Posted - 08/29/2007 :  07:13:35  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
yes Madhi
you are correct

Vinod
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/29/2007 :  08:35:21  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 08/29/2007 08:38:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000