0

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?

MISNole
  • 992
  • 1
  • 22
  • 48
  • You should be doing that in the user-interface layer of your software - is there such a layer, e.g. a program which uses the database? – Andrew Morton Jan 23 '15 at 20:37
  • Believe it will ultimately be used in an SSRS report. – MISNole Jan 23 '15 at 20:42
  • Is this any use: [How do I format date and time on ssrs report?](http://stackoverflow.com/a/15976113/1115360) – Andrew Morton Jan 23 '15 at 20:45
  • Thanks Andrew, I'll let the report handle how it wants to display the time and just leave it be in the view. They might want a 24 hour time, instead of the 12 hour I was looking for. – MISNole Jan 23 '15 at 20:58
  • I expanded a bit on my comments in the light of your comments and made it into an answer for you. – Andrew Morton Jan 23 '15 at 21:23

1 Answers1

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.

Andrew Morton
  • 24,203
  • 9
  • 60
  • 84