CODEWITHSUNDEEP
asp.netsqlms-access
Eric Hickman
Eric Hickman

Reputation: 65

Use SQL Statement to ignore weekends in Microsoft Acess

I have found several topics on how to use an SQL statement to ignore weekends. I don't need to ignore holidays, just weekends. However, none of these seem to work for me. I am using an Access database within asp.net and have tried using this function:

SELECT *
FROM your_table
WHERE ((DATEPART(dw, DateCalled) + @@DATEFIRST) % 7) NOT IN (0, 1)

but the AccessDataSource doesn't seem to like that syntax. My actual query needs to search for records that are 3 business days old, meaning that if there is a record on a Friday, it would not show up until the following Wednesday.

Upvotes: 1

Views: 214

Answers (2)

HansUp
HansUp

Reputation: 97101

You have a Date/Time parameter, DATEFIRST, and you want to select rows whose DateCalled values are no more recent than 3 workdays before DATEFIRST. I interpret that to mean these are the most recent dates you want for each parameter value ...

DATEFIRST         3 workdays previous
---------------   -------------------
Mon, 01/28/2013   Wed, 01/23/2013
Tue, 01/29/2013   Thu, 01/24/2013
Wed, 01/30/2013   Fri, 01/25/2013
Thu, 01/31/2013   Mon, 01/28/2013
Fri, 02/01/2013   Tue, 01/29/2013
Sat, 02/02/2013   Tue, 01/29/2013
Sun, 02/03/2013   Tue, 01/29/2013

I tested the following query with Access 2007. It returns results which match the above DATEFIRST pattern.

PARAMETERS DATEFIRST DateTime;
SELECT *
FROM your_table AS y
WHERE y.DateCalled < DateAdd(
        'd',
        Switch(
            Weekday([DATEFIRST])=1,-4,
            Weekday([DATEFIRST])=2,-4,
            Weekday([DATEFIRST])=3,-4,
            Weekday([DATEFIRST])=4,-4,
            Weekday([DATEFIRST])=5,-2,
            Weekday([DATEFIRST])=6,-2,
            Weekday([DATEFIRST])=7,-3
            ),
        [DATEFIRST]
        );

Based on your comments, I suspect I misunderstood your intention. It seems you don't want a parameter query. Instead you want the DateCalled filter to be 3 days before the date the query is run. If so, discard the PARAMETERS clause and substitute Access' Date() function for [DATEFIRST].

SELECT *
FROM your_table AS y
WHERE y.DateCalled < DateAdd(
        'd',
        Switch(
            Weekday(Date())=1,-4,
            Weekday(Date())=2,-4,
            Weekday(Date())=3,-4,
            Weekday(Date())=4,-4,
            Weekday(Date())=5,-2,
            Weekday(Date())=6,-2,
            Weekday(Date())=7,-3
            ),
        Date()
        );

Upvotes: 3

gdoron
gdoron

Reputation: 150253

You can use weekday:

SELECT *
FROM your_table
WHERE weekday(date) NOT IN (1, 7)

Upvotes: 3

Related Questions