Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

this query:

select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual

will return:

Eighteenth MARCH , Two Thousand Fourteen

i am wondering if there are any additional formatting keywords that will allow including a list of characters like "of" , "the" ... inorder to have an output like:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen

or is that requires splitting the date conversion and using a concat function? thanks

for example :

select 'Today is the ' || to_char(sysdate, 'Ddspth') || ' of' || to_char(sysdate,' MONTH, Yyyysp') from dual
share|improve this question
You'll have to concatenate. – Phil Mar 18 '14 at 16:21
@Phil it is actually possible...see my answer below – joe Mar 18 '14 at 16:31
up vote 7 down vote accepted

figured this out, here is a solution in case someone needs to do something similar...

using double quotes "" around characters will display them as they are...

select to_char(sysdate,'"Today is the" Ddspth "of" fmMONTH,Yyyysp') as Today from dual

will display:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen
share|improve this answer
Excellent find. Couldn't see that in the docs anywhere. – Phil Mar 18 '14 at 16:50
@joe: the manual page:… – miracle173 Mar 18 '14 at 17:02
that's cool, thanks for sharing – joe Mar 18 '14 at 17:10
@miracle173 brilliant, thanks – Phil Mar 20 '14 at 1:30

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.