May 19, 2010 at 8:15 am
I've got another challenge, but the opposite way now:
I've gotta import some old outsourced data into our own housed tables, but the values for timestart and timeend are as 12/10/2009 6:34 PM; 12/10/2009 6:34 PM
I need to convert these to the original value I was trying to convert from for Reporting purposes: timestart timefinish
12717734301271783938
I guess it would be the reverse of what Lutz suggested:
SELECT DATEADD(ss,1271782698,'19700101')
/*Result
2010-04-20 16:58:18.000
*/
How would I do this?
thx,
John
May 19, 2010 at 9:24 am
To do the opposite, you can simply get the difference in seconds between yor date column value and Jan 1 1970, i.e.
select
datediff(second, '19700101', '12/10/2009 6:34 PM') timestart,
datediff(second, '19700101', '12/10/2009 7:34 PM') timeend;
This will result in
timestart timeend
----------- -----------
1260470040 1260473640
Oleg
May 19, 2010 at 9:54 am
That's It, Oleg.
Thanks
May 20, 2010 at 7:48 pm
I quickly scanned the thread so I may be missing some details (and I've GOTTA get outta here) but you don't even really need to convert the 2 "seconds since some base time" to datetimes to get hh:mm:ss format of the time difference. Just subtract one from t'other and away you go.
As long as you don't have to get a difference >= 100 hours, this works... and if you DID need to exceed 100 hours (and didn't mind seeing something like "HHHHH:MM:SS") it'd still work with a change on the 1st line after the SELECT so the HH would not be forced to 2 digits.
--convert seconds to hh:mm:ss format
--handles up to 99:59:59 if you want HH to be 2 digits
declare @ss as integer, @hhmmss as char(8)
-- don't take my word... test it....
set @ss= 3661-- 1 hr 1 min 1 sec
set @ss= 3600 + 120 + 3-- 1 hr 2 min 3 sec
set @ss = (3600 * 3) + (60 * 2) + 30-- 3 hr 2 min 30 sec
select
RIGHT('00' + cast(@ss/3600 as varchar(2)) + ':',3)
+ RIGHT('00' + cast((@ss%3600)/60 as varchar(2)) + ':',3)
+ RIGHT('00' + cast((@ss - ((@ss/3600) * 3600))%60 as varchar(2)),2)
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply