CODEWITHSUNDEEP
sql-server-2008date
Rashid Abib
Rashid Abib

Reputation: 189

Select Query To Show Data From Friday To Thursday

My date field that this should be based off of is shipdate how can I run a Select statement and have it return data from Friday to Thursday without hard coding in dates?

Something like 

Select * from shippinginfo where shipdate between 'Friday' and 'Thursday'

For a little clarity this is what I want to achieve The where clause should be between Friday and Thursday of the previous week. So for example this week it would be 08/28/2015 - 09/03/2015 but next week it would be 09/04/2015 - 09/10/2015

Upvotes: 0

Views: 1416

Answers (2)

jpw
jpw

Reputation: 44921

If you always want to return the interval between last Friday and this Thursday you can use date functions like below.

This assumes that the week starts on Monday, if not you'll have to adjust it a bit.

select * from shippinginfo 
where shipdate between dateadd(day, -3, getdate()-datepart(DW, getdate())) 
                   and dateadd(day,  4, getdate()-datepart(DW, getdate()))

The logic is: take the current date and subtract the number of the weekday and then either subtract 3 to get last Friday or add 4 to get this Thursday.

I'm sure it can be improved, but it should give you an idea.

As xQbert correctly mentioned in a comment this might give incorrect results if your shipdate column is a datetime value (due to issues with the time component) and if so you shoudl either modify the logic or cast the shipdate to date.

Upvotes: 2

Rohan Khude
Rohan Khude

Reputation: 4903

For this atleast you have to provide the date which is friday(as computer doesn't know is it friday). and +6 to that date is thursday.

For ex:-

step 1 :- You passed the date :- 14/08/2015(FRI)

step 2 :- +6 in date :- 20/08/2015(THU)

NOTE :- Whatever u pass +6 days increment is done. So if u want friday as first then pass friday date.

Upvotes: 0

Related Questions