Post reply

time - missing colon :

  • 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'

  • 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

  • July 24, 2017 at 9:49 am

    #1952411

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

    John

  • 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

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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)?:) 🙂 :)?
    Health & Nutrition
    Rent Servers for Income (picks and shovels strategy)

  • 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)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply