Determine if Oracle date is on a weekend?

Question

Is this the best way to determine if an Oracle date is on a weekend?

select * from mytable
where 
TO_CHAR (my_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN');

Devindevina

Actually the week end is not Saturday/Sunday in all countries, a handful (20-30) countries have week end on different days (source : en.wikipedia.org/wiki/Workweek_and_weekend) – Edva

Answer 1

As of Oracle 11g, yes. The only viable region agnostic alternative that I've seen is as follows:

SELECT *
FROM mytable
WHERE MOD(TO_CHAR(my_date, 'J'), 7) + 1 IN (6, 7);

Decastere

@Michael-O - because Julian dates are basically a count of days since a fixed, known point in time you can use basic maths to determine whether or not it was a weekend or not, because you already know what day of the week day 1 was. Since other calendars have varying month lengths, year lengths, leap days and leap years, it is more difficult to derive information about the day of the week for other calendars, given a single date to work with. – Decastere

to_char(my_date, 'd') does't do the trick? why using 'J' and MOD? – Stylolite

Answer 2

Not an answer to the question. But some more information. There are many more SQL tricks with date.

to_char(sysdate, 'd') --- day of a week, 1,2,3 .. to 7
to_char(sysdate, 'dd') --- day of a month, 1,2,3 .. to 30 or 31
to_char(sysdate, 'ddd') --- day of a year, 1,2,3 .. to 365 or 366
to_char(sysdate, 'w') --- week of a month, 1,2,3,4 or 5
to_char(sysdate, 'ww') --- week of a year, 1,2,3 .. to 52

More here: to_char function.

Gabo

none of these solve the problem, the first is dangerous as "day of week" is region dependent, for example: 1 = Sunday in the US, Monday in the UK. – Decastere

True and accepted. And I dint claim to answer the question, just added more information. Your answer did solve the problem. – Gabo

Answer 3

MOD(TO_CHAR(my_date, 'J'), 7) + 1 IN (6, 7)

is NOT correct! The day number of weekend days may be 6 & 7, or 7 and 1, depending on the NLS-settings.

So the PROPER test (irrespective of NLS-settings) is this one mentioned before:

TO_CHAR (my_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN')

Assignat

I think you may be incorrect here, the J format represents Julian date, the number of days since a fixed point in time, and is completely independent of NLS settings. See this article for reference: en.wikipedia.org/wiki/… – Decastere

Answer 4

In my opinion the best option is

TO_CHAR (my_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN')

Beachhead

If you agree with someone else you should upvote them, not add duplicate code. – Murguia

Answer 5

set NLS_TERRITORY before

alter session set NLS_TERRITORY=SWEDEN;

So you are sure which numbers are weekends

Redhead