-1

I have a variable as time(7), but it gives me values like: '17:25'. I'd like for it to be in this format: '5:25 PM'. Is there a way to do this in SQL 2012 without doing something like this:

DECLARE @aux NVARCHAR(8)='16:45:00'
SELECT CONVERT(VARCHAR(15),CAST(@aux AS TIME),100)

Code gotten from here

Community
  • 1
  • 1
dotnetN00b
  • 5,021
  • 13
  • 62
  • 95
  • 1
    Even though you have quoted [this](http://stackoverflow.com/questions/11745958/sql-format-time-object-from-24-hour-to-12/11746597#11746597) page, do you not think that your question is identical? – Alexander Craggs Oct 06 '14 at 13:13
  • Formatting is almost always best handled by your presentation layer. Is there any reason you are adverse to using the solution you have posted? – GarethD Oct 06 '14 at 13:14
  • @GarethD - I just found it odd that the time variable didn't allow the time to be in different formats. And I just seeing if there were other methods in SQL 2012 to do this that wasn't in the earlier versions of SQL Server. – dotnetN00b Oct 06 '14 at 13:18
  • don't confuse how it is internally stored, its default display format, and alternate display formats. This is stored numerically and must be formatted for any display. the default display is not what you are after, so you must format it. the CONVERT you provide, is how you format it. SQL Server is strong on the data heavy activities, but light on the finesse formatting features which are usually handled by the application. – KM. Oct 06 '14 at 13:51

2 Answers2

4

No, you cannot do anything about the format SQL Server uses to store the Time datatype. You can only use tricks like the one you mentioned at query-time to deliver the output in a desired format. Or better yet, do the formatting in the front-end application.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

you can try to use DATEPART(HH... with case statements or IF/ELSE to alter the times past noon, and CONCAT am or pm on the end

errolsql
  • 11
  • 1
  • 2
  • 4