Help converting a decimal time in db4 to an actual time in sql server.

• srobinson 596

  Mr or Mrs. 500

  Points: 539

  More actions

  October 2, 2012 at 10:25 am

  #268339

  I am pulling data from an AS400 where the actual time is stored as a decimal (example - 113804 would be 11:38:04 AM, 93804 would 9:38:04 AM) . I need to convert that to an actual time so I can do some comparisons. So the actual data could have a length of 5 or 6. So - any ideas on how to convert this?

  Thanks for the help in advance.

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  October 2, 2012 at 12:40 pm

  #1544547

  declare @input int = 113804

  -- these are the different parts that will be used

  select @input / 10000

  select (@input / 100) % 100

  select @input % 100

  -- SQL Server 2008 Query

  select convert(time, dateadd(ss, @input % 100, dateadd(mi, (@input / 100) % 100, dateadd(hh, @input / 10000, '19000101'))))

  -- SQL Server 2012 Query

  select timefromparts(@input / 10000, (@input / 100) % 100, @input % 100, 0, 0)

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• srobinson 596

  Mr or Mrs. 500

  Points: 539

  More actions

  October 2, 2012 at 1:00 pm

  #1544558

  Sweet query - works great! Thanks!