sqlsql-servertime-format

Convert time from Military to standard AM/PM Microsoft SQL

What if you just have a time field that is stored as a char and displays in military time, example (1015), and you just want to format it to standard time using sql code. Is there a way to do that?

Update:

To clarify, I am defining "standard time" as meaning 6:30 PM or 4:30AM.

Solution

  • The first step is to insert a colon into your military time:

    DECLARE @Time CHAR(4) = '1015';
SELECT  STUFF(@Time, 3, 0, ':');

    Which gives 10:15

    Then you can convert it to the Time data type:

    DECLARE @Time CHAR(4) = '1015';
SELECT  CONVERT(TIME, STUFF(@Time, 3, 0, ':'));

    Which gives 10:15:00.0000000

    Finally you can convert back to varchar, with the style 100 to get it in a 12 hour format:

    DECLARE @Time CHAR(4) = '1015';
SELECT  CONVERT(VARCHAR(7), CONVERT(TIME, STUFF(@Time, 3, 0, ':')), 100);

    Which gives 10:15AM

    Or

    DECLARE @Time CHAR(4) = '2157';
SELECT  CONVERT(VARCHAR(7), CONVERT(TIME, STUFF(@Time, 3, 0, ':')), 100);

    Which gives 9:57PM

    The standard way to deal with this though would be to store the time using the TIME datatype, and do all formatting in your presentation layer.