How to do a drop down list of weeks?

Posted on 2005-04-28
Last Modified: 2013-12-24

I am trying to figure out how to create a drop down list of weeks, instead of a specific day in the week.
I want to use the drop down in a form for running a select statement based on the week selected in the drop down.
My field in the database table is formated as specific dates (ex.  27-Apr-2005).

So, when the query runs I want the recordset to be the week of records based on what week the user chose in the drop down list.

Any examples would be most appreciated.

Question by:g118481
    LVL 35

    Expert Comment

    Well determine which day of the week you want to start with and which date.

    Then do a loop using DateAdd to add 1 week or 7 days to the start date each time.
    LVL 1

    Author Comment

    Would it work if I did a query to the table for the drop down list, then format date the output in the drop down to represent a week?

    If so, how do I code the formatting of it?
    LVL 35

    Expert Comment

    It depends on what you mean by "format date the output in the drop down to represent a week"
    LVL 5

    Accepted Solution

    Lets assume the following:

    1) You want a drop down list that contains only weeks which correspond do dates actually found in the database, not all the weeks in the year (e.g.)

    2) You can access that date like this
    <cfquery name="getDates" datasource="dsource">
       SELECT DISTINCT recDate
       FROM dTable
       ORDER BY recDate ASC

    3) The Data in the column I'm calling recDate is in ODBC Date Format

    4) You want the week to start on Sunday.

    5) You know how to do the query once you get a formatted date submitted with the form

    6) You know how to reformat the date inside the form below.

    Given all that, this should work for you:
    <cfset recWeekList="">
    <cfloop list="#recDates#" index="recDate">
          <cfset recWeekday=DayOfWeek(recDate)>
          <cfset recWeekStart=recDate-CreateTimeSpan(recWeekday-1,0,0,0)>
          <cfif ListLast(recWeekList) NEQ recWeekStart><!--- Since the query was sorted, only need to check the last week in the list --->
                <cfset recWeekList = ListAppend(recWeekList,recWeekStart)>

    <!--- And in the Form --->

    <select name="weeks">
    <cfloop List="#recWeekList#" index="weekStart">
          <cfset weekStartValue=DateFormat(weekStart,"mm,dd,yyyy")>      
          <option value="<cfoutput>#weekStartValue#</cfoutput>"><cfoutput>#weekStartValue#</cfoutput></option>

    Hope it's what you were looking for,


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
    When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now