#DateDiff("D",date, NOW())# <= #Form.Days#

[Barbera]

Hello,
I have a problem with using DateDiff in a query. #DateDiff(&quot;D&quot;,date, NOW())# <= #Form.Days#
The error is that he cannot determine the value of 'datum' but that's very very correct. Outside the query it's no problem to use the same DateDiff statement. It seems I cannot already use 'date' within the query itself. Anybody who can help me?

 

[wouter]

Maybe you can't use this within your query. Can't you use <cfset> to put the result into a variable, so you can use that in the query?

Good luck, wouter

Wouter
zure_zult@hotmail.com

http://www.woutje.com

To me, boxing is like a ballet, except there's no music, no choreography, and the dancers hit each other.

 

[Barbera]

Thanks but no, that's not possible. The variable is declared in the query so I can't use a cfset statement.

 

[jackied]

Hi Barbera.

I assume &quot;date&quot; in the DateDiff statement is the field in your database.

Whatever is between the cfquery tags is sent to the database to be processed. Because you have hashes around your DateDiff statement, CF will attempt to resolve this before going to the database.

You can do one of two things.
1.) use a datediff function your database understands and use this as part of the query (take the hashes off).

2.) work out the earliest possible date using form.days and use that in the query:

<CFSET MinDate = DateDiff(&quot;D&quot;,Form.Days, NOW())#>
<CFQUERY ... >
select ...
where [date] >= #MinDate#
</CFQUERY>

BEWARE of using datediff and Now() :- Now() returns the date and time. If you have date and time in your records, DateDiff may return different results at different times of the day.

 

[wouter]

Can't you declare the variable before you execute the query? You can uses the datediff within a cfset and use the output in your query. It's really very simple.

Wouter
zure_zult@hotmail.com

http://www.woutje.com

To me, boxing is like a ballet, except there's no music, no choreography, and the dancers hit each other.

 

[GunJack]

Hey Barbera,

The word &quot;date&quot; is usually a reserved word in databases as well as programming languages such as CF. I would suspect that renaming your variable will clear up the problem. If this is a database field and can't be renamed, try referencing it like this inside your query:

#q1[&quot;date&quot;][currentrow]#

You could alternately rename the field inside your sql syntax like this:

select date as date1 from tableName.

This would allow CF to reference it with date1 instead of the &quot;date&quot; reserved word.

Hope this helps.
GJ

 

[Barbera]

Thanks for your replies. Date is not the variable I used but I used in in this exemple to make the problem clear and simple. I will try the CFSET declaration before the query. Otherwise I will have to use a statement which the MySQL database will understand.

 

[Barbera]

Thanks Jackied, it worked! I declared <cfset MinDate = #DateAdd(&quot;d&quot;, -Form.Days, Now())#> before the query and in the query I used AND MessageDate >= #MinDate#. This was what I wanted so thanks for the solution.