How to do a drop down list of weeks?


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.

Who is Participating?
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">
   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,

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.
g118481Author Commented:
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?
It depends on what you mean by "format date the output in the drop down to represent a week"
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.