order by

Hi

i have some data which i can read ok but i need to display by date posted.
Problem is the date is stored in a field as text DD/MM/YY

using ORDER BY date_posted ASC does not give desired effect as it seems to
use only the DD

example of current output.

05/04/06
05/04/06
05/04/06
05/04/06
16/01/06
16/01/06
22/03/06
23/03/06
24/03/06

is there a way (otherthan re writing all the dates in database) to sort the
dates by order ?

Mark

Re: order by

Mark D. Smith wrote:
> Hi
>
> i have some data which i can read ok but i need to display by date posted.
> Problem is the date is stored in a field as text DD/MM/YY
>
> using ORDER BY date_posted ASC does not give desired effect as it seems to
> use only the DD
>
> example of current output.
>
> 05/04/06
> 05/04/06
> 05/04/06
> 05/04/06
> 16/01/06
> 16/01/06
> 22/03/06
> 23/03/06
> 24/03/06
>
> is there a way (otherthan re writing all the dates in database) to sort the
> dates by order ?

Whats the datatype for the column? It seems like it's not of the type DATE
which had sorted it correctly from the beginning.

One way would be trying to alter the type of the column to DATE, this may
involve that you have to store the dates and id in a temporary table.


http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html


//Aho

Re: order by

Post removed (X-No-Archive: yes)

Re: order by

Mark D. Smith wrote:

> Hi
>
> i have some data which i can read ok but i need to display by date posted.
> Problem is the date is stored in a field as text DD/MM/YY
>
> using ORDER BY date_posted ASC does not give desired effect as it seems to
> use only the DD
>
> example of current output.
>
> 05/04/06
> 05/04/06
> 05/04/06
> 05/04/06
> 16/01/06
> 16/01/06
> 22/03/06
> 23/03/06
> 24/03/06
>
> is there a way (otherthan re writing all the dates in database) to sort the
> dates by order ?
>
> Mark
>
>
You have two choices:
Better, unload the table, drop it (remembering to drop any fk
constraints that have it as target, and any views it is involved in),
recreate it with the data type of the column changed to date, reload the
table (remembering to put back all fk constraints which have it as
target and all views in which it participates). Now your order by will
work, and you are sure the data is a date.
Faster: order by substr(date_posted,7,2), substr(date_posted,1,5)

Re: order by

I recommend the first choice. Doing things right make life easier in
the long run

Re: order by

wrote in message
news:1145524568.929463.105810@e56g2000cwe.googlegroups.com.. .
> I recommend the first choice. Doing things right make life easier in
> the long run
>

thanks to all who replied, i have changed the column to date and re worked
the code.

Mark