-
Filtering weekend days
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
-
where to_char(date, 'D', nls_territory='SPAIN') not in (6, 7)
there are probably better ways
-
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
U cud use TRUNC Fn..
I often do, but not at the weekends.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
well if you use trunc your code will depend on NLS_LANG setting doesnt it?
-
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).
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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.
Last edited by JMac; 05-01-2003 at 11:10 AM.
-
war story: I could not find out how to do exponentiation in AS/400 Query - so I created a table of powers of 10
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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?
Jeff Hunter
-
If IBM won't help, you help yourself!
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|