#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    2
    Rep Power
    0

    Extract 12hour AM/PM time from a date field


    The data is stored as a date like this : 2011-02-07 19:00:00

    I need to extract it like this : 7:00 PM

    I've tried DATETIMEFORMAT() & DATE_FORMAT() these are not supported in Firebird.

    I'm wondering if anyone has built a way to handle this with CASE statements as it seems to be something that would be needed quite often.

    I can get it to display as 19:00
    using left(cast(start_date AS TIME),5) as "start"

    Any suggestions?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    2
    Rep Power
    0

    I worked it out, this is how:


    Code:
    SELECT
    date,
    CASE
         WHEN cast(left(cast(end_date AS TIME),2) AS INTEGER)>12
         THEN cast(cast(left(cast(end_date AS TIME),2) AS INTEGER)-12 AS VARCHAR(2)) || cast(right(left(cast(end_date AS TIME),5),3) AS VARCHAR(3))||'PM'
         ELSE cast(cast(left(cast(end_date AS TIME),2) AS INTEGER) AS VARCHAR(2)) || cast(right(left(cast(end_date AS TIME),5),3) AS VARCHAR(3))||'AM'
    END as "Time",
    event
    FROM EVENTS
    This pulls the relevant characters out of the date, convert them to integers so it can do the calculations, then converts it to varchar so it can concatenate the text. Yes it is long-winded but it works.

    Enjoy

IMN logo majestic logo threadwatch logo seochat tools logo