Simple date question -formatting

nelsons

Have a simple date field in mysql xx/xx/xxxx

Am querying the database, and the right format xx/xx/xxxx prints out just fine.

Need to take this date, and convert it over to a human friendly format like:

Wednesday, Jan 14th 2003

When I do the following:

print (date("l, F dS Y", '')); or I try
print (date("l, F dS Y", '$myrow[0]'));

I get December, 31st 1969 dates for everything.

Have reviewed the php manual, and the board here, and have not found a correct answer to this specific simple issue.

Any help greatly appreciated - It's been 2 days now....

nnichols

The reason your existing code does not return what you expect is because the date() function takes a timestamp as its arguement, not a date.

I would suggest you use the mysql DATE format or one of the other native DATE/TIME formats and then use DATE_FORMAT() when you retrieve the data. It then makes the field useful for searching/ordering.

nnichols

SELECT DATE_FORMAT(date_col, "%W, %b %D %Y") FROM tbl_name

nemonoman

First off check your code. Don't put single quotes around the $myrow[0] and see what happens:

print (date("l, F dS Y", $myrow[0]));

//NOT

print (date("l, F dS Y", '$myrow[0]')); //<<<<SEE THE QUOTES?

SECOND:

It may be that you are not actually storing a date. It appears from your message that you are storing a string.

MySQL and most dbs store dates as numbers, which they then format for presentation. This allows date arithmetic to be done easily.

If you really are storing

xx/xx/xxxx

You're not storing a date, IMO.

Have you defined the date column of your table as DATE or DATETIME or TIMESTAMP?

Do you INSERT or UPDATE with a statement like

SET datecolumn='2003-02-22'

Just like that (quote, year, hyphen, month, hyphen, day, quote).

You can probably convert your string xx/xx/xxxx to an actual DATE that will then format using the date() function of php, by using the strtotime() function.

$mydate=strtotime('01/31/2003');

MIGHT produce right results. You can check to see whether a conversion was done by checking.

if($mydate===-1){echo 'failed';}

I don't know whether you would confuse days and months with this format, however. 02/04/2003: 2nd April or 4th Feb?

Anyway, once you have $mydate as a DATE, you can then use dateformatting:

print (date("l, F dS Y", $mydate));

nnichols

nemonoman:

you missed one of the most important errors - the php "date()" function takes a TIMESTAMP as the optional second arguement NOT A DATE!!

nemonoman

strtodate() returns a timestamp.

MySQL DATE fields return timestamps if returned unformatted

I probably should be more careful to call them timestamps, but to me they're dates, so wtf?

nnichols

Originally posted by nemonoman
strtodate() returns a timestamp.

MySQL DATE fields return timestamps if returned unformatted

I probably should be more careful to call them timestamps, but to me they're dates, so wtf?

MySQL DATE fields DO NOT return timestamps if returned unformatted - they return unformatted dates (yyyy-mm-dd).

To return the DATE field as timestamps you have to use UNIX_TIMESTAMP(col_name) -

SELECT UNIX_TIMESTAMP(col_name) FROM tbl_name

I'm sorry to be pedantic, but it is important to get these things right when trying to help newbies.

nelsons

Solved the problem this way (for all the lurkers out there with a potential simlar issue)

Many thanks to all that answered the call for help. These boards are great.

Firstly made a function:

function make_time($date) {
$aDate = explode("-",$date);
$m = $aDate[1];
$d = $aDate[2];
$y = $aDate[0];
return date("l, F jS Y", mktime(0,0,0,$m,$d,$y));
}

Then called it after getting my record

$mydate = ($myrow[0]);
print(make_time($mydate));

Tada! YYYY-MM-DD becomes Wednesday, April 16th 2003

Again many thanks to all. Hope this saves someone else out there - the frustration of beating their head and their keyboard for 2 days.

If someone has a simpler method - lets do it.

nnichols

if the field you are retrieving is in the format yyyy-mm-dd you can format it in your select statement like this -

SELECT DATE_FORMAT(date_col, "%W, %b %D %Y") FROM tbl_name

where "date_col" is the name of your field containing dates and "tbl_name" is the table name.

So todays date - "2003-02-23" - would be returned as - "Sunday, Feb 23rd 2003".

Or to return the full month name instead of the abbrev. change the "%b" to "%M"

nemonoman

To Nnichols: thanks for noting the difference between MySQL DATE vs TIMESTAMP . I ususally use TIMESTAMP cols, and just got Sloppy.

Nelsons, I'm w. Nnichols about formatting the results using SQL before retrieving them. This can be much faster and more fun. Some kinds of functions are a lot easier to manage in SQL than PHP. I get pretty fancy with my SQL, and often return long HTML statements built from the data. For some reason this is often easier for me than building the same info in PHP.