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.

Show previous replies

J

jligAuthor

Inspiring

Thanks again everyone,

- I checked the ticketDate values in the MySQL table & the data is always in this style: 9-5-2007 or 9-11-2007
- Sorry about that last post: Here is how they are sorting:

ticketDate , ticketDateFormatted, CountTickets

9-10-2007 (null) 15
9-11-2007 (null) 15
9-4-2007 (null) 5
9-5-2007 (null) 15
9-6-2007 (null) 6
9-7-2007 (null) 9

I tried:
GROUP BY ticketDateFormatted ASC; but get same results above

I also tried just the:
SELECT STR_TO_DATE(ticketDate, '%m-%d-%Y') AS ticketDateFormatted
and get all blanks?

Thanks,
jlig

C

cf_dev2

Inspiring

Grizzly9279
> Amen to that brother :)

LOL

ThreeRavens
> The preivious developer stored them here as floats using the fix() function...
> oye what a headache.

At least they're numbers. Most date functions can convert numbers to dates. That may even be how they're stored internally. But I haven't met the date function yet that can parse "F3bruary 1, 200B' into a date ;-)

G

Grizzly9279

Participating Frequently

If your date strings are actually in the format of "September 5, 2007" (which is contradictory to the 09-5-2007 format that you mentioned in your original post), than you probably need to tweak the inner STR_TO_DATE() function call as follows:

STR_TO_DATE( ticketDate, '%M %e, %Y' )

'%m-%e-%Y' = 09-5-2007
'%M %e, %Y' = September 5, 2007

**You can read more on these date format mask values here:
http://www.webfooted.net/WebNotes/title-MySQL-date_format-num-75.html

You can then modify your query to group and order by "ticketDateFormatted", or update the CF-report to order by "ticketDateFormatted" within the report itself (or via a query of queries)

C

cf_dev2

Inspiring

> what is the '%m-%e-%Y' ), '%Y-m%-d%' )

Its a string indicating the format of the date (ie M-D-YYYY). The first one is used to tell STR_TO_DATE the format of the input. The second one tells DATE_FORMAT the output format.

Grizzly9279 has the right idea. What happens if you use only STR_TO_DATE

SELECT STR_TO_DATE(ticketDate, '%m-%d-%Y') AS ticketDateFormatted
...

J

jligAuthor

Inspiring

Thanks to all the posts!

Grizzly9279,
Your code did not error at all, but it gives me the very same results?
- Exactly what is the '%m-%e-%Y' ), '%Y-m%-d%' ) doing?
- I tried Group BY ticketDateFormatted but it just totals on a single line.
- Here is what my Query results are now:

ticketDate , ticketDateFormatted, CountTickets

September 10, 2007 (null) 15
September 11, 2007 (null) 15
September 4, 2007 (null) 5
September 5, 2007 (null) 15
September 6, 2007 (null) 6
September 7, 2007 (null) 9

Note: The ticketDateFormatted field did not display anything

I'll keep trying..
Thanks,
jlig

G

Grizzly9279

Participating Frequently

RE: cf_dev2

Amen to that brother :)

T

ThreeRavens

Known Participant

The preivious developer stored them here as floats using the fix() function...oye what a headache. I will eventually have to go through the db and change them to dates.

Eric

C

cf_dev2

Inspiring

> The ticketDate is stored in MySQL as a VarChar, not a Date

I know you may not have control over the database, but this is one reason why you should store dates as dates (not varchar).

G

Grizzly9279

Participating Frequently

I think you're going to find better results formatting the date within the query, leveraging MySQL's built-in date formatting capabilities.

Using a combination of the STR_TO_DATE() and DATE_FORMAT() functions, you should be able to reformat these dates in a YYYY-MM-DD format, which sorts nicely in any report.

**Note: the following example was coded "blind". I don't have a MySQL database available to test this against, so it may not function correctly as-is. (although it might just work if I got lucky!) My hope is to at least get you pointed in the right direction.

Example:

A

Anonymous

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