[SOLVED] Formatting date from e.g. 2008-01-03 to e.g. January 3rd 2008

[nwoottonn]

Hi all!

 

I have dates stored in a mysql database (they are date fields), they are in the format of 0000-00-00 ...

 

I've looked around for ages but I cant find a way to output them to this format: January 3rd 2008.

 

Is there a way to do this?

 

Thanks guys!

 

Nick

[PFMaBiSmAd]

Use this in your query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[947740]

http://www.w3schools.com/php/php_date.asp

 

It's pretty easy once you get used to it.

 

EDIT: too late.

[nwoottonn]

Thanks for the quick reply guys!

 

I've seen that kind of formatting whilst looking around, but I'm confused on how to output it as the month name. e.g. 1 would output January, 2 would output February etc etc...

 

Is that possible?

 

Nick

[947740]

Yes.

 

http://phpbuilder.com/manual/en/function.date.php  You just use the different letters/symbols.  You may have to search a little bit to find the ones that fit exactly what you want.

[nwoottonn]

Thanks 947740!

 

I'm almost there.

 

My date is retrieved from the database and stored in the variable $date .

 

I currently have

 

^{echo date('l dS \of F Y ');}

 

But how do I tell it to convert that $date variable into that format?

 

At the moment it just echos the current date.

 

Thanks!!

[DarkWater]

Thanks 947740!

 

I'm almost there.

 

My date is retrieved from the database and stored in the variable $date .

 

I currently have

 

^{echo date('l dS \of F Y ');}

 

But how do I tell it to convert that $date variable into that format?

 

At the moment it just echos the current date.

 

Thanks!!

 

Use DATE_FORMAT in your query.  SELECT DATE_FORMAT('timeformat_string', date) as date FROM table.  Use the appropriate MySQL date_format string.

[nwoottonn]

Hi!

 

Thank you for your support by the way

 

I have this query -

 

$query  = "SELECT DATE_FORMAT('l dS \of F Y', fromm) AND DATE_FORMAT('l dS \of F Y', too) as date FROM terms ORDER BY fromm";

 

But it returns empty fields.

 

My SQL is correct i think, im not getting any errors...

[DarkWater]

Hi!

 

Thank you for your support by the way

 

I have this query -

 

$query  = "SELECT DATE_FORMAT('l dS \of F Y', fromm) AND DATE_FORMAT('l dS \of F Y', too) as date FROM terms ORDER BY fromm";

 

But it returns empty fields.

 

My SQL is correct i think, im not getting any errors...

 

$query = "SELECT DATE_FORMAT('%M %D, $Y', fromm) as date1 AND DATE_FORMAT('%M %D, $Y', too) as date2 FROM terms order by fromm";

[nwoottonn]

Hi Darkwater,

 

I'm getting the following error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in J:\WOS\www\gp\viewterms.php on line 54

 

Argh!

[DarkWater]

Hi Darkwater,

 

I'm getting the following error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in J:\WOS\www\gp\viewterms.php on line 54

 

Argh!

 

Show 5 lines above and below the error.  And the line with the error, obviously.

[DarkWater]

$query = "SELECT DATE_FORMAT(fromm, '%M %D, $Y') as date1 AND DATE_FORMAT(too, '%M %D, $Y') as date2 FROM terms order by fromm";

 

Haha, I mixed up the arguments.  It'll work now.  Just use that query.

[nwoottonn]

Hi!

 

            <table border="0" cellspacing="0" cellpadding="2">
              <tr>
                <td width="127"  bgcolor="#FFFFFF"><span class="style9">From</span></td>
                <td width="132"  bgcolor="#FFFFFF"><span class="style9">To the end of</span></td>
              </tr>
              <tr>    
<?
$query = "SELECT DATE_FORMAT('%M %D, $Y', fromm) as date1 AND DATE_FORMAT('%M %D, $Y', too) as date2 FROM terms order by fromm";$result = mysql_query($query);

THIS IS THE OFFENDING LINE >>>>>>>>>>> while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
?>  
<td bgcolor="#FFFFFF"><span class="style7"><? 

			echo ($row['fromm']); 

[nwoottonn]

$query = "SELECT DATE_FORMAT(fromm, '%M %D, $Y') as date1 AND DATE_FORMAT(too, '%M %D, $Y') as date2 FROM terms order by fromm";

 

Haha, I mixed up the arguments.  It'll work now.  Just use that query.

 

Hi darkwater!

 

Just read your new post - i still get the same error even with the updated SQL

[DarkWater]

Replace: $query = "SELECT DATE_FORMAT('%M %D, $Y', fromm) as date1 AND DATE_FORMAT('%M %D, $Y', too) as date2 FROM terms order by fromm";$result = mysql_query($query);

 

 

With:

$query = "SELECT DATE_FORMAT('%M %D, $Y', fromm) as date1 AND DATE_FORMAT('%M %D, $Y', too) as date2 FROM terms order by fromm";

$result = mysql_query($query) OR die(mysql_error());

 

Put it on two lines like that, it's easier to read.  And tell me what error appears.

 

[PFMaBiSmAd]

One or both of you might want to read the mysql manual for the date_format() function. The date is the first parameter and the format string is the second parameter.

 

DarkWater, please don't quote every post when you make a reply. We know you are replying to what was posted, we just read it ourselves and don't need to see everything twice.

[DarkWater]

$query = "SELECT DATE_FORMAT('%M %D, $Y', fromm) as date1, DATE_FORMAT('%M %D, $Y', too) as date2 FROM terms order by fromm";

$result = mysql_query($query) OR die(mysql_error());

 

Fixed.  I totally missed the error.  You had select date_format() AND date_format(), but it should be date_format(), date_format().  That query should work.

[Barand]

darkwater, instead of bumbling on all night giving misleading advice why don't you take time out to read the manual as PFMaBiSmAd suggested and come back when you know what you are doing?

[DarkWater]

darkwater, instead of bumbling on all night giving misleading advice why don't you take time out to read the manual as PFMaBiSmAd suggested and come back when you know what you are doing?

 

I need to look at which lines I copy and paste from previous posts. D:  I copied the wrong line.  Sorry about that. >_>

 

$query = "SELECT DATE_FORMAT(fromm, '%M %D, $Y') as date1, DATE_FORMAT(too, '%M %D, $Y') as date2 FROM terms order by fromm";

 

 

Does that look right, Barand? =/  Don't go crazy because I copied the wrong line. 

[Barand]

My date is retrieved from the database and stored in the variable $date .

 

I currently have

 

^{echo date('l dS \of F Y ');}

 

But how do I tell it to convert that $date variable into that format?

 

At the moment it just echos the current date.

 

Thanks!!

 

<?php
$date = '2008-02-03';                            // date from database

echo date('l jS \of F Y ', strtotime($date));    // Sunday 3rd of February 2008 
?>

[nwoottonn]

Hi again! Thanks so much for you helping me..

 

            <table border="0" cellspacing="0" cellpadding="2">
              <tr>
                <td width="127"  bgcolor="#FFFFFF"><span class="style9">From</span></td>
                <td width="132"  bgcolor="#FFFFFF"><span class="style9">To the end of</span></td>
              </tr>
              <tr>    
<?
$query = "SELECT DATE_FORMAT('%M %D, $Y', fromm) as date1 AND DATE_FORMAT('%M %D, $Y', too) as date2 FROM terms order by fromm";
$result = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
?>  
<td bgcolor="#FFFFFF"><span class="style7"><? 

			echo ($row['fromm']); 

			?></span></td>
                <td bgcolor="#FFFFFF"><span class="style7"><? echo ($row['too']);  ?></span></td>
              </tr> <? } ?>

 

That's the code...

 

Here is the error i get:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND DATE_FORMAT('%M %D, ', too) as date2 FROM terms order by fromm' at line 1

[nwoottonn]

Barand!!

 

Thank you so much - echo date('l jS \of F Y ', strtotime($date));

 

It worked great first time! And no need to modify the SQL

 

Thanks again, Solved!

 

Nick