Determine if Oracle date is on a weekend?

Marcus Leon picture Marcus Leon · Aug 10, 2010 · Viewed 59.3k times · Source

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');

Answer

ninesided picture ninesided · Aug 10, 2010

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);