Converting a date to a day of the week

I try and avoid making blog entries about discoveries I make on the web. After all, why duplicate information?

However, on this occasion Tanel Poder suggested I did after he and I both faced the same issue within days of each other. What is even more bizarre is we both made the same “mistake” whilst developing the same code!!!

We were both writing code to filter performance information based on “windows” that enabled us to eliminate weekend and overnight periods.

The code, unsurprisingly, required us to determine the day of the week for a given date.  The obvious way to do this is:

SELECT TO_CHAR(date '2010-01-16','D') FROM dual;

However, the results of this expression are non-deterministic. If the setting of NLS_TERRITORY is AMERICA then you get 7, indicating the last day of the week i.e. Saturday. However, for some reason Oracle believes that when NLS_TERRITORY is set to UNITED KINGDOM that Monday should be considered the start of the week and so you get 6. I do not know why this is as I was brought up in the UK to believe that Sunday was the first day of the week!

The problem is that NLS_TERRITORY  is set for the Oracle client installation, not just the server so people in different parts of the world get different results for the same query. You could, of course, change the settings for the session explicitly. You might also think that you could use the third argument to TO_CHAR to explicitly specify the NLS_TERRITORY for that particular call. However, despite references to the contrary in the documentation this doesn’t work.

I discovered a neat solution to this problem in Laurent Schneider’s blog here.

SELECT MOD(date '2010-01-16' - date '1000-01-01',7) FROM dual;

In general, Monday will be 0, Tuesday will be 1 and so on with Sunday being 6. So in our case, Saturday will be 5 regardless of the setting of NLS_TERRITORY.