cfoutput query within HTML Select

[Libby_H]

I have am update screen that allows the users to update the 'payment date' on a record. A simple query should load the available dates to be used to update this value.

The value that appears on the screen is the current value of get_record.payment_date, but when you click on the drop-down arrow, you get all the values in the calendar table, not just the answerset from the query. Any ideas why?

[CF_output]

Your stating that query Calendar is returning results that are less than today's date, and is not type "B" ?

[Dan_Bracuk]

What might be causing the problem is that you are using an ambiguous string in your query instead of a date object.

If your db has a function that returns the current date, use it. Otherwise, use createdate to create your variable instead of dateformat.

[Libby_H]

CF_ouput, yes the query results have only records that are of 'type B'. if the payment_date is 08/10/2008, then the dropdown list looks like:

08/10/2008
01/01/2006
01/02/2006

Where 01/01/2006 happens to be the first entry in the table.

[CF_output]

What happens when you run that query within sql (replacing the variables with values).

It looks like the dates are being compared with strings which is probably the problem, as Dan mentioned.

[Libby_H]

I run a similar query on an input screen, and it works correctly. The only difference in this query, is that I am looking for then next day to be 'todays_date'. This query extracts the correct records.

<cfset date_today=#Now()#>
<cfset todays_date=#dateformat(dateadd('d',1,date_today), "MM/DD/YY")#>

<cfquery name="calendar" datasource="#attributes.datasource1#" >
SELECT date FROM calendar
WHERE (date >= '#todays_date#')
AND type = 'B'
order by date
</cfquery>

[Libby_H · Accepted solution]

Thanks cf_output and Dan -
I've changed my query to the following, and it works (I took Dan's suggestion and used the db representation of the current date/time, in SQL Server it is GetDate(); I've added the mask to get just the MM/DD/YYYY format):

<cfquery name="calendar" datasource="#attributes.datasource1#">
SELECT date FROM calendar
WHERE calendar.date >= (Convert(varchar(10), GetDate(),101))
AND calendar.type = 'B'
order by date
</cfquery>