|
|
Posted by Bob Stearns on 10/11/50 11:45
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)
Navigation:
[Reply to this message]
|