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’
CodePudding user response:
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
CodePudding user response:
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')