[SOLVED] Formatting MySQL DATE value using PHP

[mfallon]

OK, so I can format a timestamp, and have done so without issue in my site.

 

date("j/m/y, H:i P ",mysql_result($result,$i,"users.timestamp"))

 

I have converted one of my fields which I was storing a date in as a VARCHAR to date as when I tried to use the MIN function to get the earliest date I would get whatever was first in the month, so 01/01/2009 was seen as lower than 02/01/1999.

 

The problem I have now is that the format which the DATE type stores values in is YYYY-MM-DD.

 

Does anyone know how I can convert this to DD/MM/YYYY or d/m/Y?

 

If I use the following it seems to treat it as a timestamp and I just get 31/12/1969.  ???

 

date("j/m/Y",mysql_result($result,$i,"sdate"));

 

Any help would be greatly appreciated.

 

The next question in case anyone can answer it at the same time is how I can convert from DD/MM/YYYY as a user entered value to YYYY/MM/DD when submitted in to the DB.

 

Thanks in advance, Matt 

[corbin]

First question:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

 

Second questions:

 

You could just do it with explode.  strtotime might work too though.

[mfallon]

Thanks very much corbin, managed to get the DATE_FUNCTION to work, like a muppet, I had tried to use this as a PHP function when I saw it instead of MySQL.

 

Still not certain about putting the date in, seems like explore() is going to be the best option as I don't want to store a timestamp as the field gets updated everytime the record does as I understand it and I want the record's date value to be static.

 

Thanks again for your help.

 

Matt

[larsen1492]

To convert date between PHP and MySQL date is very easy with date() and strtotime() function.

Here is an example:

http://n1scripts.com/php_scripts/php_convert_date_to_mysql_date?show=scripts&id=21