Converting Date/Time to Just Time

Question

I've got a field that expresses time as:

1900-01-01 07:00:00.000

But I'd like to convert it so it just 07:00 AM. The closest I can find is:

Convert(Varchar(20), DT.EarlyShiftStart, 114)

That gives me:

10:30:00:000

But I'd like to drop the milliseconds and add AM/PM. Anyone know the correct value?

Answer 1

Data and the display of the data are best kept separate. That makes it easier to tweak what the user sees without having to delve into the insides of the programming. For example, if you had your database output datetimes as strings, then it would be more difficult to have the display of those datetimes in different formats - if you wanted to display it in local time then it would have to be converted back to a time, adjusted, and then converted back to a string. If you've thrown away the date information by removing it at the database layer then that might be impossible. Even changing from 12- to 24-hour format would be hassle.

As the data is (likely) to be used in SSRS, it is better to take advantage of the formatting capabilities present in that. For example, you could do what you want with something along the lines of

=Format(yourTime, "hh:mm tt")

in SSRS. Then, if you wanted to show 24-hour time in one particular part of the report, it would just be a case of using something like

=Format(yourTime, "HH:mm")

and anywhere else in the report which needed 12-hour time could stay as it is.