Time Format issues

• gary.vaughan 25041

  Newbie

  Points: 9

  More actions

  February 13, 2014 at 8:40 am

  #144851

  pulling from a ms-dos csv file, time format is just numbers, no ":"

  if event time is 145345 it shows as 145345, if it is 23 seconds after midnight it shows as 23

  How do I convert or substring it to read 000023?

• Keith Tate

  SSCrazy Eights

  Points: 9938

  More actions

  February 13, 2014 at 8:52 am

  #1688788

  You can try this:declare @timevar int

  select @timevar = '145345'

  select right('000000' + cast(@timevar as varchar(6)), 6)

  select @timevar = '23'

  select right('000000' + cast(@timevar as varchar(6)), 6)

  ---

  
  Microsoft Certified Master - SQL Server 2008
  Follow me on twitter: @keith_tate

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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 13, 2014 at 9:00 am

  #1688794

  I'd go a little further and get the data as time.

  declare @timevar int

  select @timevar = '145345'

  select CAST( STUFF( STUFF( right('000000' + cast(@timevar as varchar(6)), 6), 5, 0, ':'), 3, 0, ':') AS time)

  select @timevar = '23'

  select CAST( STUFF( STUFF( right('000000' + cast(@timevar as varchar(6)), 6), 5, 0, ':'), 3, 0, ':') AS time)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2