Convert String to Time Format

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  March 13, 2013 at 11:26 am

  #272106

  Hello Everyone

  I have been working most of the morning to try and figure out how I can convert a string to a time format. So far, I have not found anything that will allow this in SQL Server.

  So, I have come up with another way, unless someone has a nifty convert function in their toolbox.

  I have a string that is taken from a file name. And I would like to store that in time format column in a table. I am already storing the date portion, which seems to convert just fine.

  This is my example of a string that I have:

  012345

  I need to insert a colon after every 2 digits, just like a time would be formatted.

  01:23:45

  I can easily convert this to a Time(0) format. As long as the colons are in place.

  But, if for some odd reason, the string would happen to come by as '12345', missing the leading zero.

  How can I handle that so that the time format would still be correct?

  Either way, I need this to be able to be converted to a Time(0) format.

  Thank You in advance for all your assistance, suggestions, and comments.

  Andrew SQLDBA

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  March 13, 2013 at 11:31 am

  #1596636

  Hi Andrew,

  I just discovered this undocumented function recently:

  declare @timestring varchar(10) = '12345'

  select cast(msdb.dbo.agent_datetime('19000101', @timestring) as time(0))

  Any good?

  Cheers

  Gaz

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 13, 2013 at 11:49 am

  #1596640

  or you can use something like this:

  DECLARE @time TABLE (StringTime VARCHAR(6))

  INSERT @time

  SELECT '112345'

  UNION SELECT '12345'

  UNION SELECT '2345'

  UNION SELECT '345'

  UNION SELECT '45'

  UNION SELECT '5'

  UNION SELECT ''

  ;WITH cte AS

  (

  SELECT RIGHT('000000' + StringTime, 6) AS FixLenStrTime -- make your time to have 6 digits always

  FROM @time

  )

  SELECT CAST(STUFF(FixLenStrTime,3,2,':' + SUBSTRING(FixLenStrTime,3,2) + ':') AS TIME(0)) -- now it will be time!

  FROM cte

  ORDER by 1 desc

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 13, 2013 at 11:59 am

  #1596643

  Or:

  DECLARE @time TABLE (StringTime VARCHAR(6))

  INSERT @time

  SELECT '112345'

  UNION SELECT '12345'

  UNION SELECT '2345'

  UNION SELECT '345'

  UNION SELECT '45'

  UNION SELECT '5'

  UNION SELECT ''

  ;WITH cte AS

  (

  SELECT RIGHT('000000' + StringTime, 6) AS FixLenStrTime -- make your time to have 6 digits always

  FROM @time

  )

  SELECT cast(stuff(stuff(FixLenStrTime,5,0,':'),3,0,':') as time(0))

  FROM cte

  ORDER by cast(stuff(stuff(FixLenStrTime,5,0,':'),3,0,':') as time(0)) desc

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  March 13, 2013 at 12:19 pm

  #1596654

  Thank You Everyone

  All your suggestions and samples worked perfectly.

  Andrew SQLDBA