Get data for only Mondays in a large dataset

Question

How to get data for only Mondays from a dataset that has daily entries in SQL Server.

I tried this query but doesn’t give any result

select *
from table ame
where field-date > ‘07-31-2022’
and datepart(weekday, field-date) = ‘0’

Answer 1

You can use datename() instead:

select *
from table ame
where field-date > ‘07-31-2022’
and datename(weekday, field-date) = 'Monday'

If you want to use datepart() then avoid single quote around integer value (in my regional setting value of Monday is 2):

select *
from table ame
where field-date > ‘07-31-2022’
and datepart(weekday, field-date) = 2

Answer 2

The value for monday may differ for datepart(weekday) depending on your regional settings. One way to avoid problems with that is to query that value from a know monday, like 5 sep 2022 for example

Also note that I use an ISO format for the dates that works in any regional setting

select *
from   table ame
where  field-date > '20220731'
and    datepart(weekday, field-date) = datepart(weekday, '20220905')

Answer 3

You can also try this

select count(*)
from payment
where extract(dow from payment_date ) = 1

It will count the data for Monday