Solved

determining the date of the last Sunday

Posted on 2009-05-05
10
455 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:Lee R Liddick Jr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 16

Expert Comment

by:duncancumming
ID: 24307056
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())
0
 
LVL 27

Accepted Solution

by:
azadisaryev earned 200 total points
ID: 24307070
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...

Azadi
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 200 total points
ID: 24307072
<cfset daysOffset = 1 - DayOfWeek(now())>
<cfset sundayThisWeek = dateAdd("d", daysOffset, now())>
<cfset sundayLastWeek = dateAdd("ww", -1, sundayThisWeek)>
<cfset sundayTwoWeeksAgo = dateAdd("ww", -2, sundayThisWeek)>
...

<cfoutput>
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>
</cfoutput>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Assisted Solution

by:duncancumming
duncancumming earned 100 total points
ID: 24307128
<!--- 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 --->

0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24307163
... 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)>

Azadi
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24307178
... Are you getting the impression you can do this with the dateAdd function ? ;-)
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24307554
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.
0
 

Author Comment

by:Lee R Liddick Jr
ID: 24307749
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!
0
 

Author Closing Comment

by:Lee R Liddick Jr
ID: 31578119
Thanks to all!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24307791
Yep.. but it is nice when the solution is simpler than you think, rather than the other way around ;-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
HTTPS for signup & login - HTTP for all other pages 10 85
Need multiple Group By's 8 55
Need to create and populate a column map table 5 62
T-SQL: Wrong Result 7 39
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question