René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

July 24, 2005: On to_char, dates and weekdays

I was born on the 28th of August in 1970. I believe, my parents told me that this date was a Friday. Unfortunately, it's too long ago for me to remember the day myself, but I'd like to verify if this date was indeed a Friday. How would I do that with Oracle?
Probably the easiest way is to use to_char with the day format string: 
SQL> select to_char(to_date('28.08.1970','dd.mm.yyyy'), 'day') from dual;
The select statement returns: 
friday
So, indeed, I was born on a friday.
Vielleicht, a german speaking person would like to have the result of the query in german rather than in english. How would she go for that?
Of course, she can alter the nls_language: 
SQL> alter session set nls_language=german;
SQL> select to_char(to_date('28.08.1970','dd.mm.yyyy'), 'day') from dual;
In fact, the result is now freitag which is german for friday: 
freitag
Then again, she might not want to alter the nls_language for the session. 
SQL> alter session set nls_language=french;
SQL> select to_char(to_date('28.08.1970','dd.mm.yyyy'), 'day', 'nls_date_language=german') from dual;
Although the session's language is now french, the result is still in german: 
freitag

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.