DateDiff question

[sanek]

Here's the problem:

I'm letting a user search a database (MySQL) and one of the search fields is a date field. The user can then enter any date and I query the database to find any date that fits the query. I also have a field that specifies how precise the search should be. Here's the query:

Begin is the date column in the database:

<cfif IsDefined(&quot;form.match&quot AND IsDefined(&quot;form.date_begin&quot><cfif #Len(Trim(Form.match))# AND #Len(Trim(Form.date_begin))# >

AND Abs(DateDiff('d',Begin,#CreateODBCDate(Form.date_begin)#) )<= '#Form.match#'

</cfif></cfif>


This worked fine with mdb database but now I get an error:

ODBC Error Code = 42000 (Syntax error or access violation)


[TCX][MyODBC]You have an error in your SQL syntax near '('d',Begin,{d '2001-05-02'}) )<= '0' Order By ID DESC' at line 4


SQL = &quot;Select * From E Where 1=1 AND Abs(DateDiff('d',Begin,{d '1900-05-02'}) )<= '0' Order By ID DESC&quot;

Data Source = &quot;SQL&quot;

Thanks in advance

Alexander

 

[Glowball]

With no pound signs around Abs() and DateDiff() I think you're making the assumption that those are MySQL functions rather than CF functions. If you want to use MySQL functions you should check out

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

and do something like

SELECT * FROM E
WHERE 1=1 AND TO_DAYS(#Begin#) - TO_DAYS(#FORM.date_begin#) <= 30;

or whatever. If you look at the error code you're getting, it sent the following SQL to the database:

Select * From E Where 1=1 AND Abs(DateDiff('d',Begin,{d '1900-05-02'}) )<= '0' Order By ID DESC

So id did not interpret Begin, Abs or DateDiff but it sent them to the db, which doesn't understand those functions or commands or variables.

You may get around this just my moving the pound signs around so they catch all CF code. Good luck!