Tony’s Oracle Tips

January 16, 2010

Converting a date to a day of the week

Filed under: Uncategorized — tonyhasler @ 2:32 pm

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.

About these ads

3 Comments »

  1. I’ve always thought of Monday as first day of the week, and I live in the UK, as Sat/Sun is the WEEKend. Seem to come down to business week or religious week.

    Monday is the the ISO8601 standard … http://en.wikipedia.org/wiki/ISO_week_date

    Interesting post.

    Comment by Andrew Campbell — January 31, 2010 @ 7:37 am | Reply

  2. [...] Session Time Zone Versus Database Time Zone Filed under: Uncategorized — tonyhasler @ 1:19 pm It is funny how you start to learn lots of new things about Oracle when you start working in an environment that is different from ones that you have worked with before. I am currently working with a Windows laptop with UK regional settings and am connecting to databases in New York via a standard Oracle client. I have a colleague in Singapore accessing the same databases and that is how I discovered the weird NLS_TERRITORY issue in this earlier post. [...]

    Pingback by Session Time Zone Versus Database Time Zone « Tony’s Oracle Tips — April 11, 2010 @ 2:45 pm | Reply

  3. Admiring the persistence you put into your site and
    in depth information you offer. It’s great to come across a blog every once in a while that isn’t the same outdated rehashed information.
    Fantastic read! I’ve bookmarked your site and I’m adding your RSS feeds to my Google account.

    Comment by Immigration Lawyer Barking — February 14, 2013 @ 4:34 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 33 other followers

%d bloggers like this: