0

I am trying to dig data from a table that has a field named begdate and another one named enddate.
They hold obvious values, but I need to have the query find the record that has a begdate that matches whatever the previous Sunday was to the day the request is made.
So, since today is Saturday 1-26-2008 I need the record that has a begdate of 1-20-2008.
How do I get the query to count back from the day of the request to the previous Sunday and then figure out that date to come up with my critera and I need to consider there will be times it crosses months - e.g. the month changes in the middle of the week.
Thanks in advance for the help.

2
Contributors
1
Reply
2
Views
9 Years
Discussion Span
Last Post by Zadj
0

Hi sprdthword,

I've had to do this in the past and found that there are several pitfalls due to the day that the week starts can be changed on the server. I eventually found a solution that appears to always work. It has been running production code for a while now with no hiccups.

Here is a little code snippet that will give you the previous sunday.

SELECT DATEADD(d, -((@@DATEFIRST + DATEPART(dw, GETDATE()) -1) % 7), GETDATE())

@@DATEFIRST is the day of the week that the database is using as the start of the week.
The -1 is the part that changes which day you are looking at (eg -2 is Monday)

Let me know if this does the job for you.

Zadjil

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.