[SOLVED] Help with date formatting

[jaikob]

I have set mysql to append the date to a field automatically, it adds the date in this form: "2008-06-21 21:13:09"

 

I want to format "2008-06-21 21:13:09" to be displayed: 6-21-2008, and for another page I need that string to look like: 6/21/08

 

How would I achieve this?

[akitchin]

is the datetime in its own column?  when pulling it from the database, look at using the DATE_FORMAT() function on the column.

[jaikob]

that is PHP, not SQL right?

[jaikob]

that is PHP, not SQL right?

 

I am not finding any references in google.

[akitchin]

no, that is MySQL. the reason i suggest using MySQL functions is because it takes far less work than PHP - MySQL is used to manipulating its own format. to implement it, you'd go:

 

SELECT stuff, DATE_FORMAT(date_field, 'string according to MySQL manual') AS formatted_date FROM table

 

formatted_date will now contain the format you're looking for. have a look in the MySQL manual under "Functions and Operators", the section for date/time functions, for a description of what that second parameter needs to look like.

 

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

[jaikob]

Edit:

 

duh, I forgot the colons.

 

Thank you so much for your help!

 

no, that is MySQL.  the reason i suggest using MySQL functions is because it takes far less work than PHP - MySQL is used to manipulating its own format.  to implement it, you'd go:

 

SELECT stuff, DATE_FORMAT(date_field, 'string according to MySQL manual') AS formatted_date FROM table

 

formatted_date will now contain the format you're looking for.  have a look in the MySQL manual under "Functions and Operators", the section for date/time functions, for a description of what that second parameter needs to look like.

 

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

[akitchin]

it would take a fair bit of string manipulation to format it using PHP.  what did the query look like, and what error were you getting?

 

if you're insistent on using PHP, have a look at the following functions:

 

explode()
substr()
str_replace()
implode()

 

you may end up using any or all of them.

[jaikob]

Thank you, I got it with SQL.

 

Thanks!