19

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');
Marcus Leon
  • 55,199
  • 118
  • 297
  • 429
  • 1
    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) – nt_1 Apr 28 '14 at 14:48

5 Answers5

29

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);
ninesided
  • 23,085
  • 14
  • 83
  • 107
  • 1
    @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. – ninesided Sep 14 '15 at 07:52
  • 1
    `to_char(my_date, 'd')` does't do the trick? why using `'J'` and `MOD`? – towi May 23 '19 at 07:37
9

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.

Guru
  • 2,331
  • 6
  • 31
  • 48
  • 7
    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. – ninesided Aug 10 '10 at 16:28
  • True and accepted. And I dint claim to answer the question, just added more information. Your answer did solve the problem. – Guru Aug 11 '10 at 17:13
6
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')
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Jan-Hendrik van Heusden
  • 85
  • 1
  • 3
  • 2
    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: http://en.wikipedia.org/wiki/Julian_day#Finding_day_of_week_given_Julian_day_number – ninesided Oct 09 '13 at 09:03
3

In my opinion the best option is

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

Carl0s1z
  • 4,683
  • 7
  • 32
  • 47
Victor
  • 41
  • 1
1

set NLS_TERRITORY before 

alter session set NLS_TERRITORY=SWEDEN;

So you are sure which numbers are weekends

Joakim
  • 513
  • 1
  • 6
  • 10