Recordset Date ordering

[The_FedEx_Guy]

Hi,
I have a date field in my database that displays the date a task was posted. But when I ORDER BY date it will only order the date by the DAY only.

The problem is that I get these values:
02/02/09
03/12/08

I want it to beable to sort it out by the year also.

So have 08 above 09

Is there anything I can do?

[Newsgroup_User]

The_FedEx_Guy wrote:
> Hi,
> I have a date field in my database that displays the date a task was posted.
> But when I ORDER BY date it will only order the date by the DAY only.
>
> The problem is that I get these values:
> 02/02/09
> 03/12/08
>
> I want it to beable to sort it out by the year also.
>
> So have 08 above 09
>
> Is there anything I can do?

What datatype is your date column in your database?

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

[The_FedEx_Guy]

I am using varchar. I was trying to avoid the YYYY format.

[Newsgroup_User]

.oO(The_FedEx_Guy)

> I have a date field in my database that displays the date a task was posted.
>But when I ORDER BY date it will only order the date by the DAY only.
>
> The problem is that I get these values:
> 02/02/09
> 03/12/08
>
> I want it to beable to sort it out by the year also.
>
> So have 08 above 09
>
> Is there anything I can do?

(assuming MySQL)

First make sure that you use the correct type for the date column,
either DATE or DATETIME. Then use ORDER BY on the date as it is stored
in the database, not on some formatted output. If you have something
like this for example

SELECT
...,
DATE_FORMAT(yourDate, '...') AS yourDate
FROM ...
WHERE ...
ORDER BY yourDate

you'll get the wrong result. You have to give the formatted date another
alias name, so that you can still access the raw date in the table:

SELECT
...,
DATE_FORMAT(yourDate, '...') AS yourFormattedDate
FROM ...
WHERE ...
ORDER BY yourDate

Now ORDER BY will work correctly on the raw date, which is YYYY-MM-DD in
case of MySQL.

HTH
Micha

[The_FedEx_Guy]

how do I get the date dd-mm-yy?
I dislike looking at the year first. :'(

[Newsgroup_User]

.oO(The_FedEx_Guy)

>how do I get the date dd-mm-yy?
>I dislike looking at the year first. :'(

DATE_FORMAT() ?

Micha