Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Category Archives: SQL

Notes on Extracting the Day of the Week

There are a couple of date formats which can be used to extract the day of the week.

D – Returns a number from 1 to 7 indicating the day of the week

Day – Returns the name of the day of the week

Here are these two formats in use:

1

2

But there is a catch, using the ‘D’ format will return different values depending on the the NLS_TERRITORY that is set. While some cultures consider Sunday to be the first day of the week, others grant that privilege to Monday. Consequently, if the NLS_TERRITORY is set to AMERICA, Saturday will be the seventh day of the week starting from Sunday and a ‘7’ will be returned. Setting the NLS_TERRITORY to ITALY, however, will return ‘6’ indicating that Saturday is the sixth day of the week starting from Monday.

3

With the ‘Day’ format, Oracle returns the “Name of day, padded with blanks to display width of the widest name of day in the date language used for this element“. In English, Wednesday is the day with the longest name with a length of 9 characters. Hence the ‘Day’ format returns the name of the day padded with blanks till the ninth character.

4

I wouldn’t have come across the quirks of these two date formats if I hadn’t burnt my fingers recently using one of them. So, if you are reading this, please consider yourself warned 🙂