[De.camm]

I have problem to get the equal comparison date in query part, here is my query code:

<cfquery name="qGetReportData" datasource="#Application.fw.Config.DSN#">
    SELECT * FROM View_Alumni
    WHERE studentcode IS NOT NULL
    <cfif #url.Course# NEQ ''>
    AND course_code = '#url.Course#'
    </cfif>
    <cfif #url.Yr_study# NEQ ''>
    AND YEAR(yearstudy) = '#url.Yr_study#'
    </cfif>
    <cfif #url.RegDate# NEQ ''>
    AND #DateFormat(createdate,'dd / mmm / yyyy')# EQ #DateFormat(url.RegDate,'dd / mmm / yyyy')#<!---HERE THE PROB--->
    </cfif>
    ORDER BY student_name, studentcode
</cfquery>

The database column date format as the attachment "createdate_column.jpg",
the form design figure as attachment "Searching_Form.JPG",
and the error come out as the attachment named "Error.jpg".

What should I do to make the database "createdate" column same as the searching form date format?

Attached image(s)

• 
• 
• 

[Craig328]

Hey De.camm.

Okay, this will be an issue of understanding just what CF does when using certain tags. In this case, the CFQUERY tag. The offending code in your example is right here at line 11:

AND #DateFormat(createdate,'dd / mmm / yyyy')# EQ #DateFormat(url.RegDate,'dd / mmm / yyyy')#<!---HERE THE PROB--->

One of the nice things that CF has that other web dev languages can lack is clear and understandable error messages. Not all CF errors are precise and point you to the exact spot of the problem, but this one does. SQLSERVER JDBC DRIVER along with incorrect syntax near '04' and the highlighted line 26 of your page code tells you that the error you're dealing with isn't a CF error per se but rather a SQL error.

Now, in your code on your quoted line 11 you're trying to craft a conditional AND phrase to the WHERE clause of the query. So, in CF we use the CFQUERY tag to pass along SQL to the database to run and return a result back to the webserver. That means that whatever gets generated inside the CFQUERY block has to be correct and formatted properly for the database that will be executing it. In your case, the issue concerns the first part of your statement: DateFormat(createdate,'dd / mmm / yyyy').

The DateFormat function you're using there is a CF function. But look at what you're applying it against. You're asking CF to format the createdate field that's in the database. CF can't format a value it doesn't yet have, so that option is out. You CAN pass in commands in SQL that tell the database to use a native SQL function. DateFormat though isn't a valid SQL Server function. In your case, you'll want to use SQL Server's CONVERT() function.

What's happening inside the CFQUERY tags is we build the SQL command we want to send to the database for execution. Since we don't have the createdate value we can't do a format on it. That value resides on the database so we need to use the database equivalent of whatever it is we want to do. Oddly though, because we do have the url.RegDate value, the dateformat function you have for that should work fine.

That should start you on your way. Post back here when/if you run into more issues.