Exclude weekends from my query

Question

I have a query which is working fine. The query basically retrieves data between 6-8 days old from current date. I'd like exclude the weekends when measuring the age of the data.

Example: If a record is registered Friday, then Monday morning it will show like it's 4 days old, but it's actually only 2 days old, because Satuday and Sunday shouldn't count.

I tried this, which does not seem to be working:

Select id, name, CreatedDate
from table
where 
CreatedDate <= DATEADD(day, -6, GETDATE()) AND CreatedDate >= DATEADD(day, -8, GETDATE()) -- here I get data between 6-8 days old
 AND ((DATEPART(dw, CreatedDate) + @@DATEFIRST) % 7) NOT IN (0, 1) -- Here im trying to exclude weekends

What am I doing wrong?

score 2 · Answer 1 · answered Apr 10 '17 at 13:32

2

you can try:

     WHERE DATEPART(dw, date_created) NOT IN (1, 7);

answered Apr 10 '17 at 13:32

SergeS

61
5

3

This will work, assuming DATEFIRST = 7. Also, i would just say weekday instead of dw for clarity. – S3S Apr 10 '17 at 13:33
Would this measure the age of data without counting the weekends in the measurement? – pancake Apr 10 '17 at 13:41
No this is a replacement for AND ((DATEPART(dw, CreatedDate) + @@DATEFIRST) % 7) NOT IN (0, 1) – SergeS Apr 10 '17 at 13:42

TriV · Accepted Answer · 2017-04-11T13:49:36.120

You could see calendar and try this query

// Logic is very simple. 
// See calendar and try.
// If today is Monday, then Prev8workingdays will include 
// 8 working days + 2 weekends = 12 days. 
// Then result will be dateadd(day,-12, getdate()) = 12 days before today.
// Same logic for other days week
DECLARE @Prev8workingdays date = CASE
    WHEN datepart(dw, getdate()) IN (2,3,4) THEN dateadd(day,-12, getdate()) 
    WHEN datepart(dw, getdate()) IN (1) THEN dateadd(day,-11, getdate()) 
    ELSE dateadd(day,-10, getdate())
    END
DECLARE @Pre6WorkingDay date = CASE
        WHEN datepart(dw, getdate()) IN (2) THEN dateadd(day,-10, getdate()) 
        WHEN datepart(dw, getdate()) IN (1) THEN dateadd(day,-9, getdate()) 
        ELSE dateadd(day,-8, getdate())
    END

SELECT sd.* FROM
@SampleDate sd
WHERE sd.CreatedDate >= @Prev8workingdays AND sd.CreatedDate <= @Pre6WorkingDay

Reference link DATEADD

Hi, I just tried this. So basically this would measure the age of a record without taking weekends into account? Can you explain what the dateadd(day,-12) and (-11) does? What if i want to measure rpevious 5 days? — pancake, Apr 11 '17 at 11:57

Simran · Answer 3 · 2017-04-10T14:00:37.450

1

Try this:

Select id, name, CreatedDate
from table
where CreatedDate < GETDATE()-6 and CreatedDate > GETDATE()-8 and
((DATEPART(dw, CreatedDate) + @@DATEFIRST) % 7) NOT IN (0, 1)

edited Apr 10 '17 at 14:00

answered Apr 10 '17 at 13:54

Simran

102
1
8

Would this measure the age of data without counting the weekends in the measurement? – pancake Apr 11 '17 at 11:45

Exclude weekends from my query

3 Answers3