Date formats in PHP/MySQL

[pedro_gb]

Okay. I have already looked in the PHP manual but no clues. How do I format the date pulled from a DATE field from MySQL in PHP? I know you can format the current date however you like it but not if the data has been pulled from MySQL. How do I do this?

[firepages]

MySQL can format the date for you - check with the manual.

From the manual:
You can specify DATETIME, DATE and TIMESTAMP values using any of a common set of formats:

As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45' and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31' and '98@12@31' are equivalent.
As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122459015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
As the result of a function that returns a value that is acceptable in a DATETIME, DATE or TIMESTAMP context, such as NOW() or CURRENT_DATE.

There are some examples in the manual of selecting a date and formatting it -

------------------

[edshuck]

hi

my question is the same as the original post - i think.

here is the code i use to post and to take a look at the results, go to noevalley.com and click on library and then noevalley and read the events toward the bottom of the page.

the problem is that i want the date and time in the form Wednesday May 2, 7 PM. But the problem is that i cannot yet break into an understanding of the coding process.

Thanks much in advance.

ed

// Display the text of each entered row in a paragraph
while ( $row = mysql_fetch_array($result) ) {
echo("<table>" . "<tr>" . '<td width="150"><FONT SIZE="2" FACE="VERDANA">' . $row["date"] . '</td><td><FONT SIZE="2" FACE="VERDANA">' . $row["message"] . "</td>" . "</tr>" . "</table>");
}

please realize that this is a work in progress and the frames will go away, the menu will be modified, etc.

[pedro_gb]

Can some nice person please post the snippet that will do this, please Thanks in advance. Hehe.

[Kevin Yank]

Nice person reporting for duty.

You just need to tell MySQL what format to return the date column in using the DATE_FORMAT() MySQL function in your SELECT query:

SELECT DATE_FORMAT(dateTimeColumn,'%W %M %e, %l %p') FROM tableName;

The key is the string '%W %M %e, %l %p', which indicates to MySQL how to format the date and time.

%W -- Weekday name
%M -- Month name
%e -- Day of the month in numeric form
%l -- Hour in numeric form, 12-hour time
%p -- AM or PM

For a full description of DATE_FORMAT() and the relevant codes, see the MySQL Reference Manual at http://www.mysql.com .

------------------
-Kevin Yank.
http://www.SitePoint.com/
Helping Small Business Grow Online!