convert military time to standard time

  • need a way (SQL) to convert military time to a standard time format. I almost got it but I'm having a little trouble.

    1338= 01:38:00 PM

    1112= 11:12:00 AM

    1135= 11:35:00 AM

    1149= 11:49:00 PM

    2014= 08:14:00 PM

    22:35= 10:35:00 PM

    Here's what I have so far; any help would do

    select mycol, Right(Convert(VarChar(30),

    Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), mycol), 6), 3,0, ':'), 6, 0, ':')), 100), 7) as timevalues from miltarytbl

  • I think it's because of where you're including the replicated 0's.

    This will work:

    DECLARE @MilDate VARCHAR(20)

    SET @MilDate = '13:38'

    SET @MilDAte = REPLACE( @MilDate, ':', '')

    SELECT @MilDate

    SET @MilDate = LEFT( @MilDate + REPLICATE( '0', 6), 6)

    SELECT @MilDate AS Timevalues

    SET @MilDate = STUFF( STUFF( @MilDate, 3, 0, ':'), 6, 0, ':')

    SELECT @MilDate

    SELECT CONVERT( VARCHAR(25), CONVERT( DATETIME, @MilDATE), 100)

    EDIT: I lied, close, but jumped the gun. Formatting is off when I apply it to convert to style 100. Back in a minute or two.

    Heh, beat myself up with a typo, whoops. All's good.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • I'm not that familiar with functions is there a way to do this with SQL? Here's what i revise from your example.

    select mycol, LEFT( mycol+ REPLICATE( '0', 6), 6)+STUFF( STUFF( mycol, 3, 0, ':'), 6, 0, ':')+ CONVERT( VARCHAR(25), CONVERT( TIME, mycol), 100)from militarytbl

  • wlblee38 (1/7/2011)


    I'm not that familiar with functions is there a way to do this with SQL? Here's what i revise from your example.

    :ermm: That was SQL. Proc based, but SQL. If you're talking about inlineSQL for a table statement...

    select mycol, LEFT( mycol+ REPLICATE( '0', 6), 6)+STUFF( STUFF( mycol, 3, 0, ':'), 6, 0, ':')+ CONVERT( VARCHAR(25), CONVERT( TIME, mycol), 100)from militarytbl

    This will have some trouble running, primarily because you broke the layering. I broke out each step above to help you see how each piece modified the data. I'll get back to you about the final, all in one, it'll take some manipulation to combine properly... and if you're using the TIME datatype as I see above, are you on SQL 2k8 or are you actually on SQL 2k?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

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

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