none
Convert Military time to Standard time??

    Question

  • I have  two fields which im trying to convert to standard time. IE (09:05 PM) or (12:00 AM).

     

    They are in Military format right now. so i have 15:45 and 21:30 etc etc.

     

    They are both Chars. And its just times and no dates. Can anyone help??

    Tuesday, December 04, 2007 8:43 PM

Answers

  • In that case there is a little more "work"; perhaps more like:

     

    Code Block

    declare @theTime varchar(10)   set @theTime = '13:50:00'

     

    select right('0' + ltrim(right(convert(varchar,
                  cast(@theTime as dateTime), 100), 7)), 7)
           as timeString

     

    /* -------- Sample Output: --------
    timeString
    ----------
    01:50PM
    */

     

     

    Wednesday, December 05, 2007 12:32 PM

All replies

  • Give a look to the CONVERT function in books online.  There might be a flavor that you can use directly.  If not, you will be able to take a substring from one of the flavors to get what you need.  Give a look at the last 5 characters of the "100" flavor.

     

    Perhaps something like:

     

    Code Block

    select right('0' + ltrim(right(convert(varchar, getdate(), 100), 7)), 7) as timeString

     

    /* -------- Sample Output: --------

    timeString

    ----------

    04:09PM

    */

     

     

    Tuesday, December 04, 2007 9:02 PM
  •  

    All that does is grab the numbers i already have, its not really converting it. Maybe theres some other way to do this. thanks for the help though.
    Tuesday, December 04, 2007 9:51 PM
  • I reread the above post and these approaches are really the same. If that one doesn't do it, this one won't either. Maybe you can explain better what you mean by convert. If you need to change the field value, then use either method in an update statement:

     

    Update mytable

        set dateStoredAsMilitary = dbo.ToCivil( dateStoredAsMilitary) -- or the statement in the post above

     

    -----------------------

     

    Here is an answer wrapped into a function. It is similar to the above code, but with a couple of twists. Turn your string into a datetime -- the day is defaulted to 1/1/1900. Turn that into a string with the 100 format (See the Convert page in Books online), but only take the last 7 characters. This gives the time with Am/Pm except with leading spaces. Remove the leading spaces with the Replace.

     

    drop function dbo.ToCivil
    go
    Create Function dbo.ToCivil( @mil char(5) )
    returns char(7)
    as
    Begin
     return Replace( Right(Convert( char(19), Cast( @mil as datetime ), 100 ), 7 ), ' ', '0' )
    End
    go

    Select dbo.ToCivil( '15:45' ),
     dbo.ToCivil( '20:04' ),
     dbo.ToCivil( '8:00' ),
     dbo.ToCivil( '23:59' ),
     dbo.ToCivil( '00:00' )


     

    03:45PM 08:04PM 08:00AM 11:59PM 12:00AM

     

    Best practice is to not store dates or times as strings, but rather to store a DateTime. Then its best to convert in the client tool.


     

     

    Tuesday, December 04, 2007 10:15 PM
  • well maybe im doing it wrong. the field name is prograss_field, and it shows up like this 13:50:00 or 17:35:00 something like that.  I do want to show it as 1:50pm and 5:35pm, something to that extent,because the people that are going to look at these, dont really know how to read military time offhand.

     

    I've never created a function, would that be done in the stored procedure? thanks for the help.

     

    Tuesday, December 04, 2007 10:34 PM
  • In that case there is a little more "work"; perhaps more like:

     

    Code Block

    declare @theTime varchar(10)   set @theTime = '13:50:00'

     

    select right('0' + ltrim(right(convert(varchar,
                  cast(@theTime as dateTime), 100), 7)), 7)
           as timeString

     

    /* -------- Sample Output: --------
    timeString
    ----------
    01:50PM
    */

     

     

    Wednesday, December 05, 2007 12:32 PM
  •  

    Works great! thanks for the help!
    Wednesday, December 05, 2007 3:41 PM