Filtering weekend days

[andreas]

Hi,

I am looking for a way to identify all Saturdays and Sundays in a date list to exclude them in a WHERE-clause.

The final SELECT should look like:

SELECT ... FROM data WHERE (date no saturday or sunday)



Can anybody provide a hint on this subject?

Thanks,
andreas

[pando]

where to_char(date, 'D', nls_territory='SPAIN') not in (6, 7)

there are probably better ways

[abhaysk]

U cud use TRUNC Fn..

[DaPi]

Originally posted by abhaysk
U cud use TRUNC Fn..

I often do, but not at the weekends.

[pando]

well if you use trunc your code will depend on NLS_LANG setting doesnt it?

[DaPi]

One way round NLS settings could be
mod(trunc(mydate)-to_date('01-01-1900','DD-MM-YYYY'),7) not in (5,6)
It's a fudge, and won't work for dates before 1900. No doubt someone can dress it up . . . .

pando's code is more transparent and works for all dates. It may also be neat start for other definitions of a weekend (e.g. Muslim Thu & Fri).

[JMac]

SELECT ...
FROM data
WHERE TO_CHAR(date_col,'Dy') NOT IN ('Sat','Sun');

Obviously you'll have to change this if NLS isn't English language.

Looking back - very similar to Pando's!

I work with (near??) a VB developer who's created a table in one of our Oracle Databases that stores the date of every weekend for the next 5 years to help him with a similar problem. Refused to use the 'Dy' solution as he'd have to change his code.

[DaPi]

war story: I could not find out how to do exponentiation in AS/400 Query - so I created a table of powers of 10

[marist89]

Originally posted by DaPi
war story: I could not find out how to do exponentiation in AS/400 Query - so I created a table of powers of 10

And you're proud of this?

[DaPi]

If IBM won't help, you help yourself!