Link to home
Create AccountLog in
Avatar of jdana
jdanaFlag for United States of America

asked on

SQL Server - Looking to format smalldatetime as HH:MM AM or HH:MM PM

Input = 1/1/2000 02:34:00, Desired Output = 02:34 AM
Input = 1/1/2000 16:34:00, Desired Output = 04:34 PM

Presumably, I use CONVERT.  What do I use as the third argument?
SOLUTION
Avatar of sventhan
sventhan
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account


SELECT
  convert(varchar(5), DateField, 108) +
  case
  when DatePart(minute, DateField) > 12 then
    ' PM'
  else
    ' AM'
  end
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jdana

ASKER

ewangoya,

I like the CASE.  It keeps it clean.  I added a LEFT to prune the seconds.

SELECT
  LEFT(convert(varchar(5), DateField, 108),5) +
  case
  when DatePart(hour, DateField) > 12 then
    ' PM'
  else
    ' AM'
  end