cfoutput query within HTML Select

Forum|Forum|17 years ago
August 14, 2008
6 replies
865 views

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?

This topic has been closed for replies.

Correct answer Libby_H

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>

L

Libby_HAuthorCorrect answer

Inspiring

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>

L

Libby_HAuthor

Inspiring

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>

A

Anonymous

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.

L

Libby_HAuthor

Inspiring

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.

D

Dan_Bracuk

Inspiring

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.

A

Anonymous

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded