Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 at 16:21
    
@Phil it is actually possible...see my answer below –  joe Mar 18 at 16:31

1 Answer 1

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 at 16:50
2  
@joe: the manual page: docs.oracle.com/cd/E11882_01/server.112/e41084/… –  miracle173 Mar 18 at 17:02
    
that's cool, thanks for sharing –  joe Mar 18 at 17:10
    
@miracle173 brilliant, thanks –  Phil Mar 20 at 1:30

Your Answer

 
discard

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.