Inspiring

Question

Sorting Dates in CF Report SQL

Forum|Forum|18 years ago
September 11, 2007
19 replies
1507 views

I'm having trouble sorting my data by ticketDate because the data is stored in two different formats:
- Database has allowed entries in both mm-dd-yyyy and mm-d-yyyy

How do I get my SQL query in CF Report writer to Sort the ticketDate correctly?
- Currently it is doing the following:
09-11-2007
09-2-2007
09-3-2007
09-4-2007

Here is my SQL:
----------------------
SELECT ticketDate, Count(tickets.ticketDate) as CountTickets
FROM tickets
WHERE (ticketType LIKE 'TS%')
GROUP BY ticketDate;

I think need to force the mm-d-yyyy data to mm-dd-yyyy then query the data but do not know how..?
(The ticketDate is stored in MySQL as a VarChar, not a Date)
Thanks,
jlig

This topic has been closed for replies.

J

jligAuthor

Inspiring

Thanks Mi-ul,
Neither option worked, still get the same syntax error.

M

Mi-ul-vmijiI

Known Participant

The date part should be d not day.

Also I'm never quite sure which bits need quote marks. If the above doesn't fix it try

dateadd("d", "-30", "#getdate()#") - actually if the first suggestion doesn't work try just the d in quote marks.

Mi-ul

J

jligAuthor

Inspiring

If I may ask one more question..

How do I query the last 30 days only?

Here is what I have tried thus far:
----------------------------------------------
SELECT owner, ticketDate, Count(tickets.ticketDate) as CountTickets, STR_TO_DATE(ticketDate, '%M %e, %Y' ) AS ticketDateFormatted
FROM tickets
WHERE (ticketType LIKE 'TS%') AND (ticketDateFormatted > dateadd(day, -30, getdate())
GROUP BY ticketDateFormatted ASC
---------------------------------------------------------

I'm getting syntax error on the second part of the Where clause?

Thanks,
jlig

J

jligAuthor

Inspiring

Thanks to all the posts above.

Here is the final SQL that worked: (the blanks I was getting were caused by using "dashes" instead of "slashes")
----------------------------------------------
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")
Order BY dueDateFormatted ASC
--------------------------------------------------

Thanks again,
jlig

C

cf_dev2

Inspiring

Yes. I ran the sql I posted earlier in a cfquery. You can see the cfdump in my previous post.

May its something wonky with CF Report Query Builder. Try running the sql as a cfquery. Then cfdump the query. What results do you get?

J

jligAuthor

Inspiring

Does it still work for you if your sample dates are like this:
9-4-2007
9-7-2007
9-10-2007

without the 0 in front of the nine..?

C

cf_dev2

Inspiring

jlig,

I don't use the Query Builder, so I can't help you out there. I can only say the sql works when run in a cfquery. MySql 4.1 should support those functions, but I would try running the sql statement as a cfquery to be certain.

query
COUNTTICKETS TICKETDATE TICKETDATEFORMATTED
1 1 09-4-2007 {ts '2007-09-04 00:00:00'}
2 1 09-7-2007 {ts '2007-09-07 00:00:00'}
3 1 09-10-2007 {ts '2007-09-10 00:00:00'}
4 1 09-30-2007 {ts '2007-09-30 00:00:00'}

J

jligAuthor

Inspiring

My MySQL table is in version 4.1,
I would love to just change the ticketDate field to a Date type but cannot.

I'm using the Query Builder in CF Report builder 8 and previewing the dump there. When I use your code above, I still just get a single line with a grand Total of all Tickets?

I have tried Grouping on my actual report but get same results.
I also tried Formatting the field on my report but it still Sorts
9-1-2007
9-10-2007
9-11-2007
etc

jlig

J

jligAuthor

Inspiring

Since the Final Output Sort is not changing, and the :
SELECT STR_TO_DATE(ticketDate, '%m-%d-%Y') AS ticketDateFormatted
gives me all blanks, then it looks like this section is not having any effect.

Somehow that line is not actually Inputing my VarChar ticketDate value & Outputting it in the proper format..?

Still referencing that MySQL links page.
jlig

C

cf_dev2

Inspiring

The query works fine with MySql 5. Are you talking about the values on the report or when you cfdump the query? And are you sure its a varchar field?

SELECT ticketDate,
STR_TO_DATE(ticketDate, '%m-%d-%Y') AS ticketDateFormatted,
COUNT(ticketDate ) as CountTickets
FROM tickets
GROUP BY ticketDateFormatted;

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded