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.
| Author |
Topic |
|
limericklad1974
Starting Member
39 Posts |
 Posted - 2012-09-21 : 10:36:20
|
| Hi thereI have a column called TimeAboveAT in my database and it is in datetime format. It has data such as:21/09/2012 01:06:1421/09/2012 03:12:34I want to strip out the hours mins and seconds and save them into columns of their own. So I have another column called Hours and it is an Int and I want to save 1 and 3 into it, based on the data above. Simililarly, I want to save 6 and 12 into the Minutes column, and save 14 and 34 into the Seconds columncan anyone help?If anyone has any ideas, that would be great.In an ideal world, I would rather get rid of the Hours column and just save hours/mins together in the Minutes column. So in the above examples, my Minutes column would store 66 and 192 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-09-21 : 10:40:30
|
why not just store that in a single column as seconds ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
limericklad1974
Starting Member
39 Posts |
Posted - 2012-09-21 : 10:47:16
|
| yes ok but how do I do that? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-09-21 : 11:19:09
|
[code]update yorutableset new_column = (datepart(hour, TimeAboveAT) * 24 * 60) + (datepart(minute, TimeAboveAT) * 60) + (datepart(second, TimeAboveAT))[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
limericklad1974
Starting Member
39 Posts |
Posted - 2012-09-21 : 11:45:03
|
| if it was possible to store it as mins and secs, can you explain how to do this? can you put hours and mins together and store them in the mins column? e.g. store 1 hr and 6 mins as 66 in the Mins column |
|
|
|
|
|
|
|
|
seperate date and time into new columns