Member Avatar for sprdthword

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
  • 62 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Zadj
Member Avatar for Zadj

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.