| Author |
 Topic  |
|
|
challapavan
Starting Member
India
8 Posts |
 Posted - 08/28/2007 : 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
USA
35007 Posts |
Posted - 08/28/2007 : 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
India
22461 Posts |
Posted - 08/28/2007 : 12:02:33
|
Where do you want to show data?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
|
|
sunsanvin
Flowing Fount of Yak Knowledge
India
1256 Posts |
Posted - 08/29/2007 : 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
India
22461 Posts |
Posted - 08/29/2007 : 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
Flowing Fount of Yak Knowledge
India
1256 Posts |
Posted - 08/29/2007 : 07:13:35
|
yes Madhi
you are correct
Vinod
Even you learn 1%, Learn it with 100% confidence. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 08/29/2007 : 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
|
Edited by - jsmith8858 on 08/29/2007 08:38:24 |
|
|
| |
Topic |
|
Extracting time from Datetime
