Time String Question 1

[Omnillas]

I have a table that, among other columns, a
6-character string that represents the time. Anyone
suggest a nice way to convert that field to a
valid non-24hour time string or a text/character
equivalent such as

example: 165304
4:53:04 PM

Possible? Suggestions?

 

[JohnDTampaBay]

This code should accomplish what you want. To make it easier to use in SELECTs, create a user-defined function that accepts the six character string and returns the nicely formatted string. HTH, Good luck!

SELECT Cast(CASE WHEN Cast(Left(Time, 2) AS tinyint) > 12
                 THEN Cast(Left(Time, 2) AS tinyint) - 12
            ELSE Cast(Left(Time, 2) AS tinyint) END
       AS varchar(2)) + ':'
       + SubString(Time, 3, 2) + ':'
       + Right(Time, 2) + ' '
       + CASE WHEN Cast(Left(Time, 2) AS tinyint) > 11
                 THEN 'PM' ELSE 'AM' END
FROM MyTable

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)