Post reply

Converting gibberish String to DateTime

  • August 17, 2018 at 3:57 pm

    #408101

    Good day fellow colleagues, so I have been querying for a bit, but I still face occasional challenges.

    So I have an SQL relational db on MS SQL Server 2014, I'm pulling a specific field, which is supposed to hold a datetime value, but instead it has this horrible gibberish which comes in as a Varchar(2000)

    0:2018061503410000:0.000000:35:0

    I need to convert this to show
    a real datetime, like "6-15-2018 03:41 AM" or even "2018-6-15 3:41"

    So far I have been able to convert it to this: 201806150341
    By using this sql line: left(replace(o.dsply_val,'0:',''), 12) as RRT_Arrival_Time1

    But after I export it to excel, I am still having a hard time either masking it to show as a date or anything. Excel wants to read it as a number, but when I convert to datetime, it doesn't read it right.

    Any help on this would be very appreciated. I already have the 25 data points for my report, but this one is kicking my butt 🙂

    Thanks everyone.

    Edward

  • August 17, 2018 at 5:09 pm

    #2002331

    If the Date/Time "Field" is always after the first colon and you only want it to the nearest minute, this will work.

    --===== This is NOT a part of the solution. It's just a test variable.
    DECLARE @test-2 VARCHAR(50) = '0:2018061503410000:0.000000:35:0'
    ;
    --===== This is one way to solve the given problem
     SELECT CONVERT(DATETIME,LEFT(STUFF(STUFF(SUBSTRING(@Test,CHARINDEX(':',@Test)+1,100),11,0,':'),9,0,' '),14))
    ;

    Add one more CONVERT to that to format it using any of the standard formats in SQL Server.  For example...

    --===== This is NOT a part of the solution. It's just a test variable.
    DECLARE @test-2 VARCHAR(50) = '0:2018061503410000:0.000000:35:0'
    ;
    --===== This is one way to solve the given problem
     SELECT CONVERT(CHAR(19),CONVERT(DATETIME,LEFT(STUFF(STUFF(SUBSTRING(@Test,CHARINDEX(':',@Test)+1,100),11,0,':'),9,0,' '),14)),100)
    ;

    If you truly need a custom format, post back.  Whatever you do, do NOT use the FORMAT function.  It's 44 times slower than even that long formula above.

    --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)

  • August 20, 2018 at 10:45 am

    #2002521

    the stuffing worked,

    thank you for your help.  Greatly appreciated..

    this is what worked for me.
    , cast(stuff(stuff(substring(o.dsply_val,3,12),11,0,':'),9,0,' ') as datetime) as RRT_Arrival_Time

  • August 20, 2018 at 2:08 pm

    #2002567

    tinoco43 - Monday, August 20, 2018 10:45 AM

    the stuffing worked,

    thank you for your help.  Greatly appreciated..

    this is what worked for me.
    , cast(stuff(stuff(substring(o.dsply_val,3,12),11,0,':'),9,0,' ') as datetime) as RRT_Arrival_Time

    Excellent.  Thanks for the feedback.

    --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)

Viewing 4 posts - 1 through 3 (of 3 total)

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