Converting to DateTime

• latingntlman

  SSCarpal Tunnel

  Points: 4348

  More actions

  May 19, 2010 at 8:15 am

  #1169449

  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

• Oleg Netchaev

  SSCertifiable

  Points: 5276

  More actions

  May 19, 2010 at 9:24 am

  #1169507

  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

• latingntlman

  SSCarpal Tunnel

  Points: 4348

  More actions

  May 19, 2010 at 9:54 am

  #1169539

  That's It, Oleg.

  Thanks

• mstjean

  Hall of Fame

  Points: 3310

  More actions

  May 20, 2010 at 7:48 pm

  #1170559

  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)

  ---

  Cursors are useful if you don't know SQL