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 |
|
inbs
Aged Yak Warrior
860 Posts |
 Posted - 2009-01-17 : 11:01:18
|
| if i have data in columns:column1: 1042-decimalcoluimn2: 12:42-varchar1.how i convert them to time i mean : 10:42:00 (what type of field that need to be keep ,i dont know if time exist?)2.after i convert them ,i want to calculate the difference between them and get 2.thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-17 : 11:21:50
|
| 1. use datetime field for both2. use DATEDIFF(hh,col2,col2) to get 2 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-17 : 11:39:55
|
SELECT *, DATEDIFF(SECOND, newColumn1, newColumn2)FROM (SELECT Column1,CAST(STUFF(REPLACE(STR(Col1, 4), ' ', '0'), 3, 0, ':') AS DATETIME) AS newColumn1,Column2,CAST(LEFT('0' + Column2, 4) AS DATETIME) AS newColumn2FROM Table1) AS d
E 12°55'05.63"N 56°04'39.26" |
|
|
|
inbs
Aged Yak Warrior
860 Posts |
Posted - 2009-01-17 : 15:43:58
|
| Peso1i do not understand your answer for column1,(what is order of the operation,what is doing first in CAST(STUFF(REPLACE(STR(Col1, 4), ' ', '0'), 3, 0, ':') AS DATETIME) AS newColumn1) .2why do you add 0 for column2? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-17 : 15:56:02
|
1) I take the numeric value and cast to varchar. Then I put a time delimeter betwen second and third position.2) Just in case time is stored as varchar 9:42, with no leading zero.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
TYPE TIME