I came up with this to get the time portion of a datetime data type formatted in
12 hour time with the AM/PM on the end:
SELECT SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 12, 6) + ' ' + SUBSTRING(CONVERT(CHAR(26), GETDATE(), 9), 25, 2) somedate
Example outputs look like 10:30 AM or 4:10 PM.
I’m not sure if this is the most efficient way, but it seems to work whether the
time has single or two digit hours both before and after noon. Replace
the GETDATE() function, which I’ve stuck in for demonstration purposes, with your
datetime column reference. If there is a better way to do this please
post a comment.