Results 1 to 8 of 8

Thread: Date Format

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Date Format

    Hi All,

    I need to display the date in the following format.

    10/21/04 as 21st day of October, 2004. How can I check if it a 1st, 2nd, 3rd or 4th number of the month? Please help. Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    1* select to_char(sysdate,'ddth "day of" FMMonth, YYYY') from dual
    SQL> /

    TO_CHAR(SYSDATE,'ddTH"DAYOF
    ---------------------------
    22th day of October, 2004
    Last edited by beilstwh; 10-22-04 at 09:54.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    to_char(sysdate,'fmddth "of" Month, YYYY')
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    Jul 2004
    Posts
    268

    Date Format

    That looks great, except if the date is 22nd the date's extenstion should be "nd" not "th", and if it is 23rd, the extension should be "rd", etc.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by inka
    That looks great, except if the date is 22nd the date's extenstion should be "nd" not "th", and if it is 23rd, the extension should be "rd", etc.
    Sigh...It is, I had typed the wrong thing in my original and just edited the string to what it should be, see below

    1* select to_char(sysdate,'ddth "day of" FMMonth, YYYY') from dual
    SQL> /

    TO_CHAR(SYSDATE,'DDTH"DAYOF
    ---------------------------
    22nd day of October, 2004
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2004
    Posts
    268

    Date Format

    Thanks a lot, it is perfect. Quick question. Which part of the query is responsible for switching from "th" to "rd" to "nd"?

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by inka
    Thanks a lot, it is perfect. Quick question. Which part of the query is responsible for switching from "th" to "rd" to "nd"?
    The "th" after the dd, it is a format flag that indicates that you want the number to be output in ordinal form.

    For an online list of the format options, see

    http://download-west.oracle.com/docs...elem.htm#34926
    Last edited by beilstwh; 10-22-04 at 11:10.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2004
    Posts
    268

    Date Format

    Great. Thanks a lot.

Posting Permissions

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