DATE: convert 2001-01-30 to....

Anon

Hello,

I have a entry in my database in the form of "2001-01-30" and I when I use that date I want the output to be in the form of
"Monday January 01, 2001"

I tried the date() function, with the proper format strings and tried to use the date entry ("2001-01-30") in my database as the timestamp... however, that doesn't work.

I figure that I would just use arrays with every month and day of the week to the get "January 01, 2001" part, but how do I get the proper day of the week?

Is there a function or method to do what I need to have done? How else should I store a date in a mysql database?

Anon

write a function that chops the yyyy-mm-dd string into $year, $month, $day.

now the function should convert these separated items into a timestamp (use mktime function).

now it could spit out the timestamp, which you can convert into any date format using date.

mithril

I'd suggestion always storing datetime info as a timestamp unless you know for a fact that the format you're planning on displaying in is the only format you'll ever need. Timestamps are much more flexible than say "2001-01-01" for the simple fact that they can be easily converted to an format you desire. That being said, read the Date and Time Functions section of the MySQL Manual. It contains all shorts of ways of manipulating datetime data within your query. Faster than making PHP do it after the fact too. Remember, your db is there only to store and manipulate information..... make it do what it was designed to do🙂

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

Cheers,

Geoff A. Virgo

Anon

MySQL has functions for this very purpose!

SELECT DATE_FORMAT( MyDataField, '%W %M %d, %Y' ) AS FormattedDate FROM MyTable;

FormattedDate will then be "Monday January 01, 2001"

For a full list of options see http://www.mysql.com/doc/D/a/Date_and_time_functions.html

steved

PHP also has functions that will allow you to convert your date format to a timestampe. Check out strtotime.

http://www.php.net/manual/en/function.strtotime.php

Anon

I did it as matto suggested

I would store the date as a timestamp if I understood what I was doing....

btw, I thought that the date entry in mysql was in the 2001-01-30 format..

I still have lots to learn, thanks for the help