time - missing colon :

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  July 24, 2017 at 9:40 am

  #338478

  i have received a legacy data files from vendor time values are sent without semicolon , can you help me how to add semicolon to it.

  create table #temp (col1 varchar(15))

  insert into #temp
select 112621
union select 92332
union select 10524
union select 184911
union select 151438
union select 171428

  --expected o/p
select '10:52:04'
union select '09:23:32'
union select '11:26:21'
union select '15:14:38'
union select '17:14:28'
union select '18:49:11'

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 24, 2017 at 9:45 am

  #1952409

  mxy - Monday, July 24, 2017 9:40 AM

  i have received a legacy data files from vendor time values are sent without semicolon , can you help me how to add semicolon to it.

  create table #temp (col1 varchar(15))

  insert into #temp
select 112621
union select 92332
union select 10524
union select 184911
union select 151438
union select 171428

  --expected o/p
select '10:52:04'
union select '09:23:32'
union select '11:26:21'
union select '15:14:38'
union select '17:14:28'
union select '18:49:11'

  Why is 10524 set as '10:52:04' instead of '01:05:24'?

  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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 24, 2017 at 9:49 am

  #1952411

  SELECT STUFF(STUFF(RIGHT('0' + '92332',6),5,0,':'),3,0,':')

  John

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  July 24, 2017 at 10:11 am

  #1952417

  Luis Cazares - Monday, July 24, 2017 9:45 AM

  mxy - Monday, July 24, 2017 9:40 AM

  i have received a legacy data files from vendor time values are sent without semicolon , can you help me how to add semicolon to it.

  create table #temp (col1 varchar(15))

  insert into #temp
select 112621
union select 92332
union select 10524
union select 184911
union select 151438
union select 171428

  --expected o/p
select '10:52:04'
union select '09:23:32'
union select '11:26:21'
union select '15:14:38'
union select '17:14:28'
union select '18:49:11'

  Why is 10524 set as '10:52:04' instead of '01:05:24'?

  i have same question to the vendor that sends data, they gave me those examples i posted. 

  there is another example 1524 should be translated to 01:52:04 instead of 15:02:04

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  July 24, 2017 at 10:31 am

  #1952426

  mxy - Monday, July 24, 2017 10:11 AM

  Luis Cazares - Monday, July 24, 2017 9:45 AM

  mxy - Monday, July 24, 2017 9:40 AM

  i have received a legacy data files from vendor time values are sent without semicolon , can you help me how to add semicolon to it.

  create table #temp (col1 varchar(15))

  insert into #temp
select 112621
union select 92332
union select 10524
union select 184911
union select 151438
union select 171428

  --expected o/p
select '10:52:04'
union select '09:23:32'
union select '11:26:21'
union select '15:14:38'
union select '17:14:28'
union select '18:49:11'

  Why is 10524 set as '10:52:04' instead of '01:05:24'?

  i have same question to the vendor that sends data, they gave me those examples i posted. 

  there is another example 1524 should be translated to 01:52:04 instead of 15:02:04

  Or 15:24:00.
  I think that you are going to need to provide some rules showing how to decompose hours/minutes/seconds in the case where the string is less than six characters long.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 24, 2017 at 11:50 am

  #1952435

  Phil Parkin - Monday, July 24, 2017 10:31 AM

  mxy - Monday, July 24, 2017 10:11 AM

  Luis Cazares - Monday, July 24, 2017 9:45 AM

  mxy - Monday, July 24, 2017 9:40 AM

  i have received a legacy data files from vendor time values are sent without semicolon , can you help me how to add semicolon to it.

  create table #temp (col1 varchar(15))

  insert into #temp
select 112621
union select 92332
union select 10524
union select 184911
union select 151438
union select 171428

  --expected o/p
select '10:52:04'
union select '09:23:32'
union select '11:26:21'
union select '15:14:38'
union select '17:14:28'
union select '18:49:11'

  Why is 10524 set as '10:52:04' instead of '01:05:24'?

  i have same question to the vendor that sends data, they gave me those examples i posted. 

  there is another example 1524 should be translated to 01:52:04 instead of 15:02:04

  Or 15:24:00.
  I think that you are going to need to provide some rules showing how to decompose hours/minutes/seconds in the case where the string is less than six characters long.

  Or 00:15:24 which would be the logical option as the zeros on the left are removed when converting to integer.
  The vendor is just mutilating the data in a way that can't be recovered.

  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

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 25, 2017 at 7:26 am

  #1952556

  mxy - Monday, July 24, 2017 9:40 AM

  i have received a legacy data files from vendor time values are sent without semicolon , can you help me how to add semicolon to it.

  create table #temp (col1 varchar(15))

  insert into #temp
select 112621
union select 92332
union select 10524
union select 184911
union select 151438
union select 171428

  --expected o/p
select '10:52:04'
union select '09:23:32'
union select '11:26:21'
union select '15:14:38'
union select '17:14:28'
union select '18:49:11'

  As Luis has already indicated, there is no possible way to accurately convert to time values based on having to convert 10524 into 10:52:04.  That simply CAN NOT BE DIFFERENTIATED from an entry to represent 01:05:24.

  No amount of SQL tricks can EVER do that.   It's just not possible.   With that kind of data, there is NO way to consistently get the correct result.   With that kind of problem, there are WAY TOO MANY possible interpretations of any given 5 digits, so the percentage of correctness for any given algorithm is probably no better than 50/50, and possibly worse.   Tell the people that supply this data that there's just no technical way to make that kind of conversion.   Not even using a well trained human to do it instead of a computer would solve this kind of problem.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  July 25, 2017 at 9:29 am

  #1952598

  sgmunson - Tuesday, July 25, 2017 7:26 AM

  mxy - Monday, July 24, 2017 9:40 AM

  i have received a legacy data files from vendor time values are sent without semicolon , can you help me how to add semicolon to it.

  create table #temp (col1 varchar(15))

  insert into #temp
select 112621
union select 92332
union select 10524
union select 184911
union select 151438
union select 171428

  --expected o/p
select '10:52:04'
union select '09:23:32'
union select '11:26:21'
union select '15:14:38'
union select '17:14:28'
union select '18:49:11'

  As Luis has already indicated, there is no possible way to accurately convert to time values based on having to convert 10524 into 10:52:04.  That simply CAN NOT BE DIFFERENTIATED from an entry to represent 01:05:24.

  No amount of SQL tricks can EVER do that.   It's just not possible.   With that kind of data, there is NO way to consistently get the correct result.   With that kind of problem, there are WAY TOO MANY possible interpretations of any given 5 digits, so the percentage of correctness for any given algorithm is probably no better than 50/50, and possibly worse.   Tell the people that supply this data that there's just no technical way to make that kind of conversion.   Not even using a well trained human to do it instead of a computer would solve this kind of problem.

  With that, I'll also say that they need to make sure their interpretation of time is actually what was intended.  I believe it was meant to be an integer (like the times found in MSDB job history tables) and that  "10524" was truly meant to represent "01:05:24".  Otherwise, agreed... there is no way to determine the value as currently stated by the OP.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)