How to write SQL that gets ‘everything since last monday’

This was an interesting challenge that was requested of me.  My client wanted a report that listed everything since the begining of the previous week, or everything since last monday.  This is interesting because the date range changes, on monday it will be today()-1, on tuesday today()-2, etc.  Using that pattern I came up with this:

SELECT *
FROM the_table
WHERE datediff(‘d’,theDateColum,Now())<=(5+weekday(now())))

The weekday() function gets 0 for Sunday, 1 for Monday, etc.  So this will always get last week’s monday. 
— Anthony

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: