Last Weeks Data on a Monday Help!!

[RJ13]

Hi Guys,



Some help here please. I have a data set with load of dates and need some SQL to show only last weeks data.

This will be run on a Monday so I need see all records from the previous Mon-Sun please

Thanks in advance

[kd2017]

You can calculate the dates for the previous week like so

WHERE [some_date_field] >= Date()-Weekday(Date(),2)-6 AND [some_date_field] < Date()-Weekday(Date(),2)+1

Date()-Weekday(Date(),2)-6 = the monday of the previous week
Date()-Weekday(Date(),2)+1 = the monday of the current week
So the code above is filtering down to the dates greater than or equal to 12:00AM last monday and before 12:00AM the current monday.

*Side note that date fields are date+time fields, so just remember to that into account when working with date fields. It might display March 22, 2020 but it could actually equal #March 22, 2020 11:17:31 AM#.