[DAL]

Can anyone suggest reason for MySQL to store dates in a strange way?
2006-04-20

Whats that all about???

Anyway I wanted to store dates in my database and ended up just setting it up as a varchar field. I now have the problem that I cannot sort the results by date. I however can sort the dates in the format above as no one will understand. I wanted to store dates dd/mm/yy but I cant find anything on the web to supply an answer.

Any suggestions

[alane]

Dal you can reformat the date virtually any way you wish using the DATE_FORMAT command.

For example one of the querry$ I use is:
IF ($table == 'manufacturer') {$query = "SELECT model, dir, file, version, manufacturer, DATE_FORMAT(dated, '%d %b %y') AS date FROM make, manuals WHERE (LEFT(manufacturer,3) = '$find') AND makeid = make.id ";}

This produces a date in the "09 Jan 06" format however by varying the combination within the DATE_FORMAT() there are almost limitless conbinations.

Check out- http://dev.mysql.com/doc/refman/5.0/...functions.html

[mab]

Alane answered the - what to do about this problem...

For the "Whats that all about???" question, as you have discovered, has to do with sorting. Sorting a yyyy-mm-dd date works as expected and also allows date comparisons.

[0o0o0]

how about.. like ..
Sunday April 29th 2006

is that possible?

Ive been trying to figure something out for a nightclubs venue list..

example:

I add in all the upcoming dates for the month.. and when that date has passed an IF statement more or less says.. is it past this date? yup.. goto else and echo " thanks for all who came out to join us"

Any ideas on how to build something like this? most importantly with the dating described above.

[mab]

Actually, your post does not contain enough information about what you want, for anyone to provide anything more than a guess.

how about.. like ..
Sunday April 29th 2006

is that possible?

Is it possible to do what with this? Convert a string entered like your's into a mysql format date? Yes - see the STR_TO_DATE function at the link alane provided in the post above.

This part of the query would look something like this - STR_TO_DATE('Sunday April 29th 2006', '%W %M %D %Y')

You can put this conversion any place in the query where you want a mysql format date out of your date string.

If you have dates stored in the database in mysql format in a field called date_col and your date is in a variable called $datestring, the following should work(I have not personally used this) in a query to find all the rows where the date is less then your date -
WHERE date_col < STR_TO_DATE('$datestring', '%W %M %D %Y')

Is is possible to take a mysql format date and output it in your format? Yes - see the DATE_FORMAT function at the link alane provided.

This part of the query in a SELECT would look something like this (assuming that the dates are in a field called date_col) -
DATE_FORMAT(date_col, '%W %M %D %Y') AS date

[DAL]

Thanks to all for your help - most appricated!