determining the date of the last Sunday

I need to figure out what the date is on the Sunday prior to the current day and the date of the Sunday the week before that.  For example, today is Tuesday May 5.  I need to capture the date variable of the Sunday of the current week which in the example would be Sunday, May 3.  Then I also need to capture the week prior to that, so I would need to capture Sunday, April 26.  

Is there even a way to do this?  I'm needing these dates because I have a reporting tool with a drop down of specific periods of time.  For example, in the dropdown I have for choices, This Week, Last Week, This Month, Last Month, This Year, Last Year.  So when the end user chooses one of these periods, it queries the database for that specific period of time.  For example, the end user chooses Last Month.  So in my query, it would get all records from April 1 to April 30.  I have that done...but not sure on how to get the weeks.  

So if the end user chooses This Week, I want the query to give results for records of between May 3 and May 9.  If the end user chooses Last Week, I want the query to give results for records between April 26 and May 2.
Lee R Liddick JrReporting AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

azadisaryevConnect With a Mentor Commented:
shouldn't be hard with a couple cf date functions. something like this:

<!--- NOTE: Sunday is day of week #1 in CF --->
<cfset datToday = now()>
<cfset dowToday = dayofweek(datToday)>
<cfset datThisSunday = dateadd('d', 1-dowToday, datToday)>
<cfset datLastSunday = dateadd('d', -7, datThisSunday)>

similar for other dates you need...

ok, each day of the week has a numeric value, from 1 (Sunday) to 7 (Monday).  I think use that to figure out what day of the week it is, work out what you then need to subtract from the current date to get the previous sunday.  The function you'll need to do that is DayOfWeek(Now())
_agx_Connect With a Mentor Commented:
<cfset daysOffset = 1 - DayOfWeek(now())>
<cfset sundayThisWeek = dateAdd("d", daysOffset, now())>
<cfset sundayLastWeek = dateAdd("ww", -1, sundayThisWeek)>
<cfset sundayTwoWeeksAgo = dateAdd("ww", -2, sundayThisWeek)>

Today is:  #DateFormat(now(), "dddd mmm dd, yyyy")#<br>
Sunday this week:   #DateFormat(sundayThisWeek, "dddd mmm dd, yyyy")#<br>
Sunday last week:   #DateFormat(sundayLastWeek, "dddd mmm dd, yyyy")#<br>
Sunday two weeks ago:   #DateFormat(sundayTwoWeeksAgo, "dddd mmm dd, yyyy")#<br>
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

duncancummingConnect With a Mentor Commented:
<!--- this will return a value between 0 (if today's Sunday) to 6 (if it's Monday) --->
<cfset numDays = Abs(1 - DayOfWeek(Now()))>

<!--- subtract #numDays# from the current date --->
<cfset startDate = DateAdd("d", -numDays, Now())>

<!--- use that in your query or whatever, and work out the endDate as 7 days after the startDate --->

... continued:

i.e. for last month:
<cfset lastMonth = dateadd('m', -1, datToday)>
<cfset lastMonthStart = createdate(year(lastMonth), month(lastMonth), 1)>
<cfset lastMonthEnd = createdate(year(lastMonth), month(lastMonth), daysinmonth(lastMonth))>

last year:

<cfset lastYearStart = createdate(year(dateadd('yyyy', -1, datToday)), 1, 1)>
<cfset lastYearEnd= createdate(year(dateadd('yyyy', -1, datToday)), 12, 31)>

... Are you getting the impression you can do this with the dateAdd function ? ;-)
Lee R Liddick JrReporting AnalystAuthor Commented:
yeah agx...if I would have only known it was this simple.  :)  Just got off of a call, let me take a look at everything.
Lee R Liddick JrReporting AnalystAuthor Commented:
These were gimme points, weren't they agx?  I'm thinking if I would have thought about it a little bit, I probably would have come up with it, but I need to get this done and a bunch of other stuff before the end of the day and figured I could use some help.  You guys are great!
Lee R Liddick JrReporting AnalystAuthor Commented:
Thanks to all!
Yep.. but it is nice when the solution is simpler than you think, rather than the other way around ;-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.