CF SQL to get Past Due Tickets Only?

Report · Dec 23, 2008

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">


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>
--------------------------------------------------------

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

Report · Dec 23, 2008

I don't think DateFormat is MySQL function and including it in your SQL statement is causing your error.

I'd recommend changing your database structure to store your dates in a DATE or DATETIME column. If this is not possible you might try the SQL below, using the MySQL CONVERT function to convert the dueDate value to a DATE value, then using the DATE value to compare to #due_Date#. Note this will probably fail if any values of dueDate cannot be converted to a DATE value.

Edit: Changed operator in SQL statement.

Report · Dec 23, 2008

Bob, Thanks for the reply..

When I use the following:
-------------------------------------------------------------
<cfset due_Date = #DateFormat(Now(), "mm/dd/yyyy")#>

<cfquery datasource="Ctickets" name="MyQuery">


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 CONVERT(DATE, dueDate) < <cfqueryparam value="#due_Date#" cfsqltype="CF_SQL_VARCHAR" /> 

Order BY dueDateFormatted ASC
</cfquery>
--------------------------------------------------------------------------

I get:
--------------------------------------------------------------------------
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) <'12/23/2008' Order BY dueDateFormatted ASC' at line 4

The error occurred in C:\Inetpub\wwwroot\Beta\reports\Ticket_Analysis_Report_Past_Due.cfm: line 18

16 : FROM tickets
17 : WHERE (status <> "Closed")
18 : AND CONVERT(DATE, dueDate) < <cfqueryparam value="#due_Date#" cfsqltype="CF_SQL_VARCHAR" /> 
19 :
20 : Order BY dueDateFormatted ASC
------------------------------------------------------------------------
I also tried changing the cfsqltype="CF_SQL_DATE" and tried no type at all, but get the same error.
Lastly, I tried to use my dueDateFormatted field, but it says "Column not defined"

I did make sure there are no "Nulls" or weird dates in the database for dueDate. (Changing the field-type is not an option)

Thanks, jaa

Report · Dec 23, 2008

Most db's have something that returns either the current date, or the current date and time. The names are not the same though. If you can figure out what it is for your db, you might find it mighty handy.

Report · Dec 23, 2008

Your db column is a date field. Dateformat returns text. That is the cause of your problem.

Converting dates to strings for your query is a bad idea to start with. You are then making it worse by choosing an unwise format.

Report · Dec 23, 2008

Does the query below work?

Report · Dec 23, 2008

All, Same error either way.

- using CONVERT(dueDate, DATE) or

- using the actual Date value of '12/23/2008'

- Will definitely check into "Views" (never done that before?)

- The <> "Closed" part works fine as is. (Just needing to add the Date criteria to it)

What is interesting to me is that the STR_TO_DATE(dueDate, '%m/%e/%Y' ) AS dueDateFormatted actually seems to create a valid Date that I use elsewhere in my CF Report? Is there a way to rewite this SQL to use it instead of the dueDate original field?

Report · Dec 23, 2008

> WHERE (status <> "Closed")

Try using single quotes, not double, around the status value 'Closed'

> AND CONVERT(DATE, dueDate) < <cfqueryparam
> value="#due_Date#" cfsqltype="CF_SQL_VARCHAR" />

2. The syntax of the CONVERT function is wrong. That is what is causing one of your syntax errors. Check the manual for the correct syntax

3. You are not comparing the same data types. Either compare two dates or two varchars, not a mix of both. Do not leave the conversion up to MySQL. It may get it right .. or it may not. Of course that is assuming the < operator will work as you expect with your date strings values.

> Changing the field-type is not an option

I agree with Dan. In the long run, it is much better to store dates as dates, not strings. However, if changing the data type is _really_ not an option, consider creating a "view". It does not require changing your table. Your view could include all of the existing columns, plus a few extras (ie the varchar dates converted to date/time values using STR_TO_DATE). Then you shoudl be able to do your SELECT/INSERTS on the "view", just as if it were a regular table.

http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

Report · Dec 23, 2008

My Bad on the CONVERT, I misread the docs.

Try this:

Report · Dec 23, 2008

quote:

Originally posted by: -==cfSearching==-

I agree with Dan. In the long run, it is much better to store dates as dates, not strings.

I thought he was doing that already based on his use of the convert function.

Report · Dec 23, 2008

Dan Bracuk wrote:
> I thought he was doing that already based on his use of the convert function.

My take on it was, he was storing the dates as varchar. Otherwise, he would not need to use convert.

Report · Dec 23, 2008

The MySQL backend has the dueDate field listed as a VARCHAR (255).
All data in that table field is either the word "NULL" or a Date value like that listed below:
(some sample dueDate values)
NULL
NULL
12/23/2008
12/23/2008
12/29/2008
12/22/2008
12/20/2008
12/19/2008

Report · Dec 23, 2008

jlig wrote:
> STR_TO_DATE(dueDate, '%m/%e/%Y' ) AS dueDateFormatted
> actually seems to create a valid Date that I use elsewhere in my CF
> Report?

Yes, that is what STR_TO_DATE does. It converts a date string to a date/time object. That is what I was suggesting you use in your view. Then you could use that column in your queries, instead of the varchar column:


WHERE dueDateAsDateColumn < Date()

> Is there a way to rewite this SQL to use it instead of the
> dueDate original field?

What do you mean? You are already doing something similar by using CONVERT(dueDate, DATE) in your WHERE clause. The difference between the functions being STR_TO_DATE offers more control over how the date string is interpreted.

Report · Dec 23, 2008

> either the word "NULL" or a Date value

Is it an actual NULL or the string/text "NULL"? There is a difference.

In either case, you could create a view using CASE. Check the value and convert it to a date/time when it is valid. Otherwise, return null. Then use that view/column in your queries instead of the "ticket" table

CREATE VIEW vTicket
AS
SELECT
DueDate,
CASE WHEN DueDate = 'NULL' THEN NULL
ELSE str_to_date(DueDate,'%m/%d/%Y')
END AS `RealDueDate`
, ... OtherColumns
FROM ticket

Of course the "view" is just making the best of a bad situation (ie storing dates as varchar).

Adobe Community

CF SQL to get Past Due Tickets Only?