date stored as a string

jonsof

My problem is that i stored the date in a field of a table as string.
in that format dd/mm/yyyy

And now i want to get ordered result concerning this date.

Is that practicable?

xblue

though it may be possible using some complicated sql statement probably involving a lot of SUBSTR operations, I'd always try to convert it into a proper date format since that should be a lot easier to handle.

jonsof

is there any script to do that job?

xblue

I don't think so, it's probably a too specific task, but you could still search code libraries.

If you write your own, since it runs only once it wouldn't even have to be the most elegant solution.

a simple approach:
loop through all records
use explode to get day, month and year
update each record with 'year-month-day' or yearmonthday
then change the column type

jonsof

what if i want to edit each row.

i have to do the inverse procedure

jonsof

Any other ideas?

EvilElvis

search the column with a LIKE statement.

You can break it down using the slashes between the fields, so you can seperate the searches to the day, month, or year.

altexis

I use to store date-time vars in a string format because it is practical if software from different frameworks edit the same data (eg. PHP and C++ Builder or Delphi)

I IS practical when you use a YY-MM-DD format. That is because you can easily order these dates as strings.

For example if you have the two dates 01-02-2003, 01-05-1999 (in a DD-MM-YYYY format) if you sort them as strings you will get:
no.1 = 01-02-2003
no.2 = 01-05-1999

this is obviously wrong

if you use a YYYY-MM-DD format the string-styled sorting will give you:
no.1 = 1999-05-01
no.2 = 2003-02-01

... so if you want to get your database properly sorted my suggestion is make a script to change the date formating from DD-MM-YYYY to YYYY-MM-DD... and everyting is going to be fine.

just remember to use always two digits for months and days, not one

kali tixi 🙂

acidbox

I agree with Elvis, use LIKE in your SELECT statement.

jonsof

Does this work properly using LIKE? because i know that LIKE is likely for searching CHAR not numbers.

acidbox

Um... I'm not sure what you mean by that, but using LIKE does work.

$sql = "SELECT * FROM table WHERE date LIKE '%/2003' ORDER BY date DESC";

That would find all the dates in 2003 and order them from newest to oldest.