Skip to main content
V
voodoofrdr
Inspiring
July 18, 2008
Answered

Get all Sundays in a month

  • July 18, 2008
  • 1 reply
  • 1757 views
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
    This topic has been closed for replies.
    Correct answer Dan_Bracuk
    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.

    1 reply

    D
    Dan_BracukCorrect answer
    Inspiring
    July 18, 2008
    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.
    V
    voodoofrdrAuthor
    Inspiring
    July 18, 2008
    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.
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    July 22, 2008
    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.


    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
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices