[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

How to do a drop down list of weeks?

Hi,

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.

Cheers
0
g118481
Asked:
g118481
  • 2
1 Solution
 
mrichmonCommented:
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.
0
 
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?
0
 
mrichmonCommented:
It depends on what you mean by "format date the output in the drop down to represent a week"
0
 
8riaNCommented:
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
</cfquery>

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)>
      </cfif>
</cfloop>

<!--- 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>
</cfloop>
</select>

Hope it's what you were looking for,
8riaN

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now