dd/mm/YYYY date problem

[stevesimo]

Hi, I am working on an application which stores details of customer orders.  The payment date has been stored as a varchar in dd/mm/yyyy UK format ie. 28/10/2008.  My problem is that when I run a query on orders and try to index on payment date the results are not properly ordered.  I would normally store dates as a timestamp value but this system was written a couple of years ago and there are a lot of records already in the database.  Can anyone offer any advice on how I can get around this problem for the purposes of indexing the results in order of payment date using the existing dd/mm/yyyy varchar format. 

 

Thanks

 

Steve

[MadTechie]

SELECT *,STR_TO_DATE('paymentdate', '%d/%m/%Y') as pdate FROM table ORDER BY pdate

 

But you should really create a new field then do

UPDATE table  SET newfield = STR_TO_DATE('paymentdate', '%d/%m/%Y')

check it then remove the 'paymentdate' and rename 'newfield' to 'paymentdate'

 

 

hope that helps

 

EDIT: backup before the update (i am not taking any blame if it goes wrong .. as you could probably find me in the UK *hide* )

but it should be fine

[revraz]

You should create a new Field with a DATE setting and use a real date format.

[MadTechie]

You should create a new Field with a DATE setting and use a real date format.

LOL, thanx for point that out, when i said create a new field.. i did mean a date field .. but didn't say that ...