Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2001
    Posts
    16

    Unanswered: Extract 'hh AM/PM' from getdate()

    Hi,
    I am looking for a query to extract hour and AM or PM value from a date on sql2000.

    ex/-
    Input : 2001-12-28 22:18:07.810 (from getdate())
    Output : 10 PM

    select convert(varchar, (datepart(hh, convert(varchar, getdate(), 8)) % 12)) + ' ' +
    substring (convert(varchar, convert(datetime, getdate(),20), 100),
    DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100)) - 1,
    DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100 )))

    The above works but is there a better way to do this?

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    This is a little shorter:

    SELECT CONVERT(VARCHAR,DATEPART(hh,GETDATE())%12) +
    CASE WHEN (DATEPART(hh,GETDATE())%12) > 0 THEN ' PM' ELSE ' AM' END

  3. #3
    Join Date
    Aug 2001
    Posts
    16
    thanks for your reply.
    but i figured that 12 AM or 12 PM was displayed as 0 AM and 0 PM.
    Hence to reduce my troubles, i will stick with the good ol' substring.

    SELECT (substring(CONVERT(VARCHAR,getdate(),22),10,2) + ' ' +
    substring(CONVERT(VARCHAR,getdate(),22), 19,2))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •