Date checking and SQL from CF pages

Date checking and SQL from CF pages

Post by Crai » Wed, 11 Jun 2003 09:04:08



This query below works fine in Access when I query the DB directly but
as I try it from a ColdFusion page no luck

SELECT rec_id, rec_start_date
FROM record
WHERE rec_rtp_id=3
AND rec_start_date < #6/9/2003#
ORDER BY rec_start_date DESC


<cfset mydate = "##" & DateFormat(CreateODBCDate(DateAdd('s', 0,
Now())), "m/d/yyyy") & "##">

<cfquery name="feature_main"  datasource="ds" maxrows=1 dbtype="ODBC">
SELECT rec_id, rec_start_date
FROM record
WHERE rec_rtp_id=3
AND rec_start_date < #mydate#
ORDER BY rec_start_date DESC

example rec_start_date in db is 8/8/2003 which shouldn't be pulled up
but it is!

Thanks for any help!


1. CF SQL and Date - CF Newbie

I am trying to filter a recordset using the current date in this format
1/23/02, and need some help.

This is what I currently have:

<cfinclude template="../Connections/connConnection.cfm">
<cfparam name="URL.csort" default="end_date">
<cfparam name="DateFormat(Now())" default="1/20/99">
<cfparam name="rsProducts__varSort" default="#URL.csort#">
<cfparam name="rsProducts__varDate" default="#DateFormat(Now())#">
<cfquery name="rsProducts" datasource=#MM_connFormaggio_DSN#
username=#MM_connConnection_USERNAME# password=#MM_connConnection_PASSWORD#>
SELECT end_date, sale_price, prom_notes, prod_name, price, prod_size,
products.prod_id FROM promotions INNER JOIN products ON products.prod_id =
promotions.prod_id WHERE promotions.end_date >= #rsProducts__varDate# AND
promotions.eff_date <= #rsProducts__varDate# ORDER BY #rsProducts__varSort#

The page passing the parameters is passing this:

<a href="../Data_Returns/listByPromo.cfm?csort=end_date">

And this is th eerror that I get:

Error Diagnostic Information
ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Hint: The cause of this error is usually that your query contains a
reference to a field which does not exist. You should verify that the fields
included in your query exist and that you have specified their names
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (6:1) to (6:135).

Ken Ford

2. Website sizing problem!

3. date checking with sql query??

4. Flash 5 - streaming audio??

5. CF Reports SQL Date Queries

6. Job Opportunity in BC

7. searching against SQL date field w CF

8. Save file from CD to HD

9. CF and SQL date queries

10. Insert current date CF & MS SQL

11. CF or SQL server date problem

12. just checking the date part of a date and time field

13. Date in MSD v Date in CF