CODEWITHSUNDEEP
sql-server
Lyubomir Ivanov Valchev
Lyubomir Ivanov Valchev

Reputation: 773

Where clause to select records which are greater than 3 days but exclude weekend days (Saturday and Sunday)

I am trying to select records in SQL Server which are greater than 3 days than the current date but I need to exclude weekend days (Saturday and Sunday).

The following SQL script works to select records older than 3 days:

select * from [dbo].[tb_Schedule]
WHERE date >= DATEADD(day, -3, GETDATE())

But the problem is I don't know how to exclude weekend days.

I know that the following Where clause is used to exclude weekend days but I don't know how to implement it in the above where clause

WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)

Please assist how I can select records which are greater than 3 days but exclude weekend days. Thanks

Upvotes: 2

Views: 766

Answers (3)

Xabi
Xabi

Reputation: 483

select * from [dbo].[tb_Schedule]
where date >= DATEADD(DD, -3 -case SIGN((DATEPART(DW, GETDATE()) + @@DATEFIRST + 5) % 7 - 3)
        when -1 then 2 else 0 end, GETDATE())

It only failed if you call this select on Sundays (returns only 2 days).

Also if you want only the date part of GetDate(), you can replace the last GETDATE() with FLOOR(CAST(GETDATE() as real))

Upvotes: 0

GreyOrGray
GreyOrGray

Reputation: 1729

If you're just excluding weekends and not holidays, you can just join the clauses with AND. This one uses WEEKDAY with DATEPART because it's easier to read. This won't work with holidays, though.

SELECT * 
FROM [dbo].[tb_Schedule]
WHERE 
    [date] >= DATEADD(DAY, -3, GETDATE())
    AND DATEPART(WEEKDAY,[date]) NOT IN (1,7)

Upvotes: 1

Luis Cazares
Luis Cazares

Reputation: 3585

Here's a formula with example on how to exclude the weekends. As we've mentioned before, consider using a calendar table as you might want to exclude holidays.

SELECT calDate, 
    DATENAME( dw, calDate), 
    DATEADD( DD, -( 3 + CASE WHEN DATEDIFF( DD, '19000106', calDate)%7 <= 2 THEN DATEDIFF( DD, '19000106', calDate)%7
                            WHEN DATEDIFF( DD, '19000106', calDate)%7 = 3 THEN 2
                            ELSE 0 END), calDate)
FROM (VALUES( CAST( '20190201' AS date)),
            ( CAST( '20190202' AS date)),
            ( CAST( '20190203' AS date)),
            ( CAST( '20190204' AS date)),
            ( CAST( '20190205' AS date)),
            ( CAST( '20190206' AS date)),
            ( CAST( '20190207' AS date)),
            ( CAST( '20190208' AS date)),
            ( CAST( '20190209' AS date)),
            ( CAST( '20190210' AS date)),
            ( CAST( '20190211' AS date)),
            ( CAST( '20190212' AS date)),
            ( CAST( '20190213' AS date)),
            ( CAST( '20190214' AS date)),
            ( CAST( '20190215' AS date)))x(calDate);

Upvotes: 1

Related Questions