how do I format date in oracle sql

as follows:
Monday, the Twenty-Fourth of May, 2009

TO_CHAR( 'fmDAY, DD MONTH, YYYY')

Its the DD part I need something else for.

Thanks!

Rolf

rolfgAsked:
Who is Participating?
 
MilleniumaireConnect With a Mentor Commented:
....I meant to point out that the case of the format model also affects the case of the result, so in my previous post, the MONTH will be displayed in upper case.  Changing it to Month will use mixed case.  The Ddspth gives you your day in words.  The Dd causes mixed case to be used and the spth spells it out:

The same example above but with complete mixed case should give you what you want:

select TO_CHAR(to_date('24-MAY-09'),'fmDay, Ddspth Month, YYYY')
from sys.dual;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you really need the spelled out words?

you could have this:
TO_CHAR( x, 'fmDAY, ') || TO_CHAR( TO_NUMBER( TO_CHAR(x, 'DD')), 'JSP' ) || TO_CHAR( x, ' DD MONTH, YYYY')

Open in new window

0
 
sdstuberCommented:
use the "th" format with some julian date math tricks


    TO_CHAR(x, 'fmDay, ')
         || ' the '
         || TO_CHAR(TO_DATE(TO_CHAR(x, 'DD'), 'j'), 'Jspth')
         || ' of '
         || TO_CHAR(x, 'fmMonth, YYYY')
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sdstuberCommented:
for example...

SELECT      TO_CHAR(x, 'fmDay, ')
         || ' the '
         || TO_CHAR(TO_DATE(TO_CHAR(x, 'DD'), 'j'), 'Jspth')
         || ' of '
         || TO_CHAR(x, 'fmMonth, YYYY')
  FROM   (SELECT   SYSDATE x FROM DUAL);

Wednesday,  the Eighth of April, 2009
0
 
johnsoneSenior Oracle DBACommented:
Since there are a known number of days in a month, why not use decode for the part you cannot get with an inherent date format.

AngelIIIs, I tried your query and got an error.
select to_char(sysdate,'fmDay') ||
', the ' ||
decode(to_char(sysdate, 'fmdd'), 
            '1', 'First',
            '2', 'Second',
            '3', 'Third',
            '4', 'Fourth',
            '5', 'Fifth',
            '6', 'Sixth',
            '7', 'Seventh',
            '8', 'Eighth',
            '9', 'Ninth',
            '10', 'Tenth',
            '11', 'Eleventh',
            '12', 'Twelfth',
            '13', 'Thirteenth',
            '14', 'Fourteenth',
            '15', 'Fifteenth',
            '16', 'Sixteenth',
            '17', 'Seventeenth',
            '18', 'Eighteenth',
            '19', 'Nineteenth',
            '20', 'Twentieth',
            '21', 'Twenty-First',
            '22', 'Twenty-Second',
            '23', 'Twenty-Third',
            '24', 'Twenty-Fourth',
            '25', 'Twenty-Fifth',
            '26', 'Twenty-Sixth',
            '27', 'Twenty-Seventh',
            '28', 'Twenty-Eighth',
            '29', 'Twenty-Ninth',
            '30', 'Thirtieth',
            '31', 'Thirty-First'
      ) ||
' of ' ||
to_char(sysdate, 'fmMonth, yyyy') FROM dual;

Open in new window

0
 
sdstuberCommented:
I also got an error in Angeliii's query.

I started down the road of big case/decode too, then remembered Jspth  will do that for us
0
 
MilleniumaireCommented:
Try the following:

TO_CHAR(x,'fmDay, Ddspth MONTH, YYYY')

eg.
select TO_CHAR(to_date('24-MAY-09'),'fmDay, Ddspth MONTH, YYYY')
from sys.dual;
0
 
johnsoneSenior Oracle DBACommented:
Much better than the decode.
0
 
sdstuberCommented:
jeez, remembered "th" but forgot about "spth"

thanks Milleniumaire!  that's the best answer yet
0
 
awking00Connect With a Mentor Commented:
See attached.
date-format.txt
0
 
rolfgAuthor Commented:
Wow, never had so many answers to a question... 150 points for Milleniumaire since he came up with Ddspth first, 100 for awking00 as that is the correct solution including "the" and "of", as apparently you don't have to use the || concat as in sdstuber's example
Thanks all that responded.
Rolf
0
 
gkhngkdmrCommented:
select to_char(trunc(sysdate),'DAY')||','||TO_CHAR(TO_DATE(TO_CHAR(sysdate, 'DD'), 'j'), 'Jspth')||' of '||TO_CHAR(SYSDATE, 'MONTH')||','||to_char(trunc(sysdate),'YYYY') from dual
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.