Exclude weekend days in my where clause filter

Question

I am using SQL Server 2014. I have the following query:

Use MyDatabase

select *
from Table1    
where [CreatedOn] between '2019-01-01' and '2019-12-31'

I need to filter by [CreatedOn] so that it excludes all Saturdays and Sundays between the specified dates.

What would be the correct syntax to perform this operation?

Does this answer your question? [How do I exclude Weekend days in a SQL Server query?](https://stackoverflow.com/questions/1803987/how-do-i-exclude-weekend-days-in-a-sql-server-query) — Dale K, Sep 14 '20 at 06:16

score 2 · Accepted Answer · answered Sep 14 '20 at 06:17

2

Where [CreatedOn] between '2019-01-01' and '2019-12-31'
  and DATEPART(WEEKDAY,[CreatedOn]) not in (6, 7)

just be careful, sunday is the first day of the week in some cultures. If that is the case you need to change not in (6, 7) to not in (7, 1)

you can check the first day of the week with SELECT @@DATEFIRST

answered Sep 14 '20 at 06:17

1

When I run SELECT @@DATEFIRST, I get 7. So, I guess I will need to use "not in (7,1)". – user3115933 Sep 14 '20 at 06:28
@user3115933 `( @@DateFirst + DatePart( weekday, CreatedOn) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. – HABO Sep 14 '20 at 20:37

1 Answers1