Reformating the date, how?

[rdruser]

I pull the date from my database as
$date=$r["date"];

it is in the standard unix format as YYYY-MM-DD. What I would like to do is have it formatted like
Mar 15, 2005

I've checked and found that one way of doing it would be like
DATE_FORMAT($date %M %D, %Y)

Unfortunately, that doesn't work and because i haven't worked on something like this before, I am not sure if it is simply a case of a typo or what to do to get it to work and would appreciate any help you could provide. Thanks in advance

[dreamcatcher]

Hi rdruser, welcome to webmasterworld. :)

DATE_FORMAT should be ok, you are just using the syntax slightly wrong. You should put it in your query and use the field name. Try this:

$query = mysql_query("SELECT DATE_FORMAT(date, '%b %d, %Y') as date_string FROM table") or die(mysql_error());

Note that the syntax for the DATE FORMAT display is not the same as the PHP date format. This example would have your date in a new variable 'date_string'. An example using a while loop would be:

while ($row = mysql_fetch_array($query))
{
echo $row['date_string'];
}

Hope that helps.

dc

[bigname]

you can also do it by:

$right_date = date("M d, Y", strtotime($date));

[gliff]

A couple of things.

First, YYYY-MM-DD isn't the standard Unix format. It just happens to be a format that your database (mysql, I assume) accepts and returns.

Second, the function you mentioned isn't a PHP function, it's a mysql function. As Dreamcatcher indicated, you'll need to use it in your query to get your date.

Third, PHP has a date formatting function that will format any date in the "Unix Timestamp" format. ([php.net ]). A Unix Timestamp is the number of seconds since January 1, 1970. You can use the mysql function UNIX_TIMESTAMP to convert any date column into a Unix Timestamp for use in PHPs date function.

//untested pseudocode
$query = mysql_query("SELECT UNIX_TIMESTAMP(date) as theTimestamp FROM tablename");
$row = mysql_fetch_array($query);
date('[format string here]', $row['theTimestamp']);

It's worth spending sometime learning about timestamps. By converting your dates to integers, you can easily perform date comparisons, as well as "date arithmetic".

[rdruser]

[i]$right_date = date("M d, Y", strtotime($date));[i]

Lists is only displaying the current date, but doesn't pull the infomation from the db.

[coopster]

dreamcatcher has offered a working solution in message #2. Have you tried that yet?

[rdruser]

If possible, I would like to use the current connection rather then opening up a new one to obtain the data.

[dreamcatcher]

Not a problem. Use:

$query = mysql_query("SELECT *,DATE_FORMAT(date, '%b %d, %Y') as date_string FROM table") or die(mysql_error());

dc

[rdruser]

Tried it and it did not format it, it still displays it as DD-MM-YYYY. Here's how I have it the code

//select the table
$result = mysql_query("SELECT *,DATE_FORMAT(date, '%b %d, %Y') as date_string FROM orders") or die(mysql_error());

if($num4 == 0) {
echo "No order history found for this account.";
} else {
//grab all the content
while($r=mysql_fetch_array($result)) {
$id=$r["id"];
$date=$r["date"];
$time=$r["time"];
$points=$r["points"];
$reward=$r["reward"];
$status=$r["status"];
$tracking=$r["tracking"];
$estimate=$r["estimate"];
$carrier=$r["carrier"];

Then the date is called up by the $date variable. Any suggestions?

[jatar_k]

no problem, the format obviously doesn't work as is so you must manipulate it

so what can make the date format we need?
Well, the first place to start is to look at is the php Date and Time Functions [php.net]

it seems that date() [php.net] should do it.

What type of input does it need?
string date ( string format [, int timestamp] )
looks like a unix timestamp

How can we convert what we have, which is YYYY-MM-DD. Well it seems that mktime [php.net] can make a unix timestamp.
int mktime ( [int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]] )

so we have month, day and year just not seperately. We just need to split [php.net] it up, ah ha.

we can use the hyphen to split on, pass that into mktime and then run it through the date function

<?
$dbdate = '2005-03-15';
$datearr = split('-',$dbdate);
$tstamp = mktime(0,0,0,$datearr[1],$datearr[2],$datearr[0]);
$myformat = date('M j, Y',$tstamp);
echo '<p>myformat: ',$myformat;
?>

no problem ;)

[rdruser]

Got it to work. Thanks!

[dreamcatcher]

Glad you got it to work. The reason why it wouldnt work is you were assigning the wrong variable.

$date=$r["date"];

should have been:

$date=$r["date_string"];

dc