Get all Sundays in a month

[voodoofrdr]

Hello,

I have a project where I have to generate a report based on week ending on Sundays for a month. In my db I have a column of week ending dates. I am really confused about how do I get the date for all the sundays for a particular month, query the db for those dates and output the data as a report like an excel format with each column being a week ending date and the rows under that column has the data for that week ending.

Please help.

Thank you

[Dan_Bracuk · Accepted solution]

Use dayofweek on the 1st day of that month.

Then add the correct number of days to get the first Sunday. Start a list of dates with that date.

Keep adding 7 days and list appending until you get to the end of the month.

That's the easy part.

For the rest of it, depending on what data you are selecting, you can either do a fancy query that ends up with your dates as column aliases,
or
run a normal query, re-arrange it into an array and output the array.

[voodoofrdr]

But this is going to be dynamic. The user will select the month/year from a drop down and based on that I have to create the report. If i get the first day of the month how will I know how many days to add to get Sunday ?

Thanks for the quick reply.

[Dan_Bracuk]

quote:

---

Originally posted by: voodoofrdr
But this is going to be dynamic. The user will select the month/year from a drop down and based on that I have to create the report. If i get the first day of the month how will I know how many days to add to get Sunday ?

Thanks for the quick reply.

---

In addition to what Ian and Phil said, the createdate function will probably come in handy.

To find out how to use any function we tell you about, google "coldfusion name_of_function x" where x is the version of cold fusion you are using. Whenever I do that, the 1st offering is the section of the cfml reference manual I want. If we suggest a tag, google "<cfTagName> x" to get similar results.

[voodoofrdr]

Thank you all for the help. I did get the data correctly, now I have to figure out how to display it all in a good excel spreadsheet look. If you have any ideas on that too I will appreciate.

[JR__Bob__Dobbs-qSBHQ2]

quote:

---

Originally posted by: voodoofrdr
Thank you all for the help. I did get the data correctly, now I have to figure out how to display it all in a good excel spreadsheet look. If you have any ideas on that too I will appreciate.

---

For creating an Excel file in ColdFusion try Ben Nadel's POI utility.


http://www.bennadel.com/projects/poi-utility.htm

[Newsgroup_User]

voodoofrdr wrote:
> But this is going to be dynamic. The user will select the month/year from a
> drop down and based on that I have to create the report. If i get the first
> day of the month how will I know how many days to add to get Sunday ?
>
> Thanks for the quick reply.
>

That is what the dayOfWeek() function Dan mentioned is for. The
documentation will tell you all about how it will return what day of the
week between 1-Sunday and 7-Saturday the given date falls on. With this
information it is an easy mathematical calculation to determine the
first Sunday of the month. Then it is a straight forward looping
calculation to find each following Sunday until the end of the month.

[paross1]

<cfset date_var = "07/01/08">
<cfset nmbr_to_add = 8-(DayOfWeek(date_var) MOD 8)>
<cfoutput>Number to add to #date_var# to get to Sunday is: #nmbr_to_add#</cfoutput>

(EDIT: Ooops, should have been MOD 8 and not 7. And, it will show 7 if you happen to use a date_var that falls on Sunday.....)

Phil