Filtering weekend days
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Filtering weekend days

  1. #1
    Join Date
    Apr 2003
    Posts
    3

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    where to_char(date, 'D', nls_territory='SPAIN') not in (6, 7)

    there are probably better ways

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    U cud use TRUNC Fn..
    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"

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well if you use trunc your code will depend on NLS_LANG setting doesnt it?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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 12:10 PM.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width