Skip to main content
J
jlig
Inspiring
December 23, 2008
Question

CF SQL to get Past Due Tickets Only?

  • December 23, 2008
  • 6 replies
  • 1435 views
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
    This topic has been closed for replies.

    6 replies

    -
    -__cfSearching__-
    Inspiring
    December 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).



    -
    -__cfSearching__-
    Inspiring
    December 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:

    <!---
    use cfqueryparam, createODBCDate, or MySQL's Date() function
    --->
    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.

    -
    -__cfSearching__-
    Inspiring
    December 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

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    December 23, 2008
    My Bad on the CONVERT, I misread the docs.

    Try this:
    D
    Dan_Bracuk
    Inspiring
    December 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.

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    December 23, 2008
    Does the query below work?
    J
    jligAuthor
    Inspiring
    December 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?

    D
    Dan_Bracuk
    Inspiring
    December 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.
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    December 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.

    J
    jligAuthor
    Inspiring
    December 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">
    <!--- 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 CONVERT(DATE, dueDate) < <cfqueryparam value="#due_Date#" cfsqltype="CF_SQL_VARCHAR" /> <!--- you may need a different cfsqltype here, I'm not a MySQL expert --->

    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" /> <!--- you may need a different cfsqltype here, I'm not a MySQL expert --->
    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
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices