Can any of you figure out how to get this SQL (see code at
bottom) to only pull tickets that are older than today?
- Currently, when I run the query, the page opens with this
error:
----------------------------------------------------------------------------
Error Executing Database Query.
Syntax error or access violation: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '(dueDate, "dd-mm-yyyy")
<23/12/2008 Order BY dueDateFormatted ASC' at line 3
The error occurred in
C:\Inetpub\wwwroot\Beta\reports\Ticket_Analysis_Report_Past_Due.cfm:
line 17
15 : SELECT ticketNum, status, ticketType, owner, acctNum,
ticketDate, ticketTime, firstName, lastName, businessName,
custAddress, city, dueDate, turnUpDate, STR_TO_DATE(dueDate,
'%m/%e/%Y' ) AS dueDateFormatted
16 : FROM tickets
17 : WHERE (status <> "Closed") AND DateFormat(dueDate,
"dd-mm-yyyy") < #due_Date#
18 :
19 : Order BY dueDateFormatted ASC
------------------------------------------------------------------
Heres my current SQL in my CF page:
--------------------------------
<cfset due_Date = #DateFormat(Now(), "dd/mm/yyyy")#>
<cfquery datasource="Ctickets" name="MyQuery">
<!--- Query below copied from CF Report Builder, modified
with form values in WHERE clause
--->
SELECT ticketNum, status, ticketType, owner, acctNum,
ticketDate, ticketTime, firstName, lastName, businessName,
custAddress, city, dueDate, turnUpDate, STR_TO_DATE(dueDate,
'%m/%e/%Y' ) AS dueDateFormatted
FROM tickets
WHERE (status <> "Closed") AND DateFormat(dueDate,
"dd-mm-yyyy") < #due_Date#
Order BY dueDateFormatted ASC
</cfquery>
<cfreport template="Ticket_Analysis_Report_Past_Due.cfr"
format="pdf" query="MyQuery">
<!--- cfreport does all the work. It substitutes above
query for the query built in the report. If no query is
specified, it uses the query in the report template. --->
</cfreport>
--------------------------------------------------------
Note:
- If I remove the AND part of my WHERE statement, I get all
Open tickets
- The dueDate field is stored in MySQL as a VARCHAR
Special thanks to all previous help on my last report.
jaa