Convert HHMMSS to HH:MM:SS

• SQL SERVER ROOKIE

  Ten Centuries

  Points: 1032

  More actions

  November 20, 2012 at 9:48 am

  #98374

  HI,

  I have a time_column (unfortunately datatype is decimal) .I have to concatenate one time_column to this date_column.

  This time column is also defined as a decimal ,I'm sure even its code is gonna be similar to the one below

  The original format is HHMMSS and I want it in HH:MM:SS.I have displayed the output below

  sELECT

  CASE WHEN MY_DATE_COLUMN> 19000000 AND MY_DATE_COLUMN < 21000000 THEN

  CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,MY_DATE_COLUMN)))

  ELSE CONVERT(DATETIME,'01/01/1900')

  END NEW_DATE_COLUMN

  FROM MY_TABLE

  MY_time_column

  123658

  171420

  102137

  102704

  111639

  162821

  105001

  84814

  120505

  122223

  170520

  92403

  93551

  95229

  121919

  123625

  102755

  94051

  95618

  101142

  141911

  144111

  151129

  151832

  114125

  122232

  133559

  134540

  143021

  144003

  144034

  182804

  130436

  151645

  143958

  144222

  143702

  150247

  151112

  134007

  0

  Thx

  SM

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  November 20, 2012 at 10:31 am

  #1560928

  Not very elegant, but... :

  declare @timec decimal(6,0)

  set @timec = 7

  select case when datalength(convert(varchar(6),@timec)) = 1

  then '00:00:0' + CONVERT(varchar(1),@timec)

  when datalength(convert(varchar(6),@timec)) = 2

  then '00:00:' + CONVERT(varchar(2),@timec)

  when datalength(convert(varchar(6),@timec)) = 3

  then '00:0' + stuff(CONVERT(varchar(8),@timec),2,0,':')

  when datalength(convert(varchar(6),@timec)) = 4

  then '00:' + stuff(CONVERT(varchar(8),@timec),3,0,':')

  when datalength(convert(varchar(6),@timec)) = 5

  then '0' + stuff(stuff(CONVERT(varchar(8),@timec),4,0,':'),2,0,':')

  else stuff(stuff(CONVERT(varchar(8),@timec),5,0,':'),3,0,':')

  end

  ---

  And then again, I might be wrong ...
  David Webb

• calvo

  SSChampion

  Points: 12930

  More actions

  November 20, 2012 at 10:40 am

  #1560932

  SELECT STUFF(STUFF(STUFF(my_time_column, 1, 0, REPLICATE('0', 6 - LEN(my_time_column))),3,0,':'),6,0,':'),

  ______________________________________________________________________________________________
  Forum posting etiquette.[/url] Get your answers faster.