reformat date

Report · Apr 20, 2008

I have a MySQL table with a field formatted with the "date" type (example:
2005-03-21). When I add this field to a web page (example: "<?php echo
$row_getNews['news_date']; ?>" ) I would like to hve it written as: "March
21, 2005".

Is that possible?
If so, how is it done?

Jeff

Report · Apr 20, 2008

On 20 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:

> I have a MySQL table with a field formatted with the "date" type
> (example: 2005-03-21). When I add this field to a web page (example:
> "<?php echo $row_getNews['news_date']; ?>" ) I would like to hve it
> written as: "March 21, 2005".
>
> Is that possible?
> If so, how is it done?

Use DATE_FORMAT in your SQL statement, thus:

SELECT field1, field2,
DATE_FORMAT(dateField, '%M %e, %Y') AS myFormattedDate
FROM myTable

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

Another option is to convert the date from ISO format to a Unix
timestamp using PHP's strtotime() function, then formatting it using
date():

<?php
// Done in several steps for clarity. Can be done in one step.

$myUnixTimestamp = strtotime($row_Recordset1['dateField']);
$myFormattedDate = date('F j, Y', $myUnixTimestamp);
echo $myFormattedDate;

?>

http://www.php.net/strtotime
http://www.php.net/date

Of the two methods, I prefer doing it in SQL.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Apr 20, 2008

Thank you Joe... I used PHP to do it and it works grest! This is my code:
<?php
$myUnixTimestamp = strtotime($row_get_News['news_date']);
$myFormattedDate = date('F j, Y', $myUnixTimestamp);
echo $myFormattedDate;
?>

Again, Thank You

Jeff

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns9A86C5E147FDmakowiecatnycapdotrE@216.104.212.96...
> On 20 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:
>
>> I have a MySQL table with a field formatted with the "date" type
>> (example: 2005-03-21). When I add this field to a web page (example:
>> "<?php echo $row_getNews['news_date']; ?>" ) I would like to hve it
>> written as: "March 21, 2005".
>>
>> Is that possible?
>> If so, how is it done?
>
> Use DATE_FORMAT in your SQL statement, thus:
>
> SELECT field1, field2,
> DATE_FORMAT(dateField, '%M %e, %Y') AS myFormattedDate
> FROM myTable
>
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
>
> Another option is to convert the date from ISO format to a Unix
> timestamp using PHP's strtotime() function, then formatting it using
> date():
>
> <?php
> // Done in several steps for clarity. Can be done in one step.
>
> $myUnixTimestamp = strtotime($row_Recordset1['dateField']);
> $myFormattedDate = date('F j, Y', $myUnixTimestamp);
> echo $myFormattedDate;
>
> ?>
>
> http://www.php.net/strtotime
> http://www.php.net/date
>
> Of the two methods, I prefer doing it in SQL.
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/contact.php

Report · Apr 20, 2008

Opps.... spoke to soon, my code:

<?php
$myUnixTimestamp = strtotime($row_get_News['news_date']);
$myFormattedDate = date('F j, Y', $myUnixTimestamp);
echo $myFormattedDate;
?>

writes December 31, 1969, no matter what the value of "news_date"
I think I'm close, but no cigar.

Any suggestions?

Jeff

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns9A86C5E147FDmakowiecatnycapdotrE@216.104.212.96...
> On 20 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:
>
>> I have a MySQL table with a field formatted with the "date" type
>> (example: 2005-03-21). When I add this field to a web page (example:
>> "<?php echo $row_getNews['news_date']; ?>" ) I would like to hve it
>> written as: "March 21, 2005".
>>
>> Is that possible?
>> If so, how is it done?
>
> Use DATE_FORMAT in your SQL statement, thus:
>
> SELECT field1, field2,
> DATE_FORMAT(dateField, '%M %e, %Y') AS myFormattedDate
> FROM myTable
>
> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
>
> Another option is to convert the date from ISO format to a Unix
> timestamp using PHP's strtotime() function, then formatting it using
> date():
>
> <?php
> // Done in several steps for clarity. Can be done in one step.
>
> $myUnixTimestamp = strtotime($row_Recordset1['dateField']);
> $myFormattedDate = date('F j, Y', $myUnixTimestamp);
> echo $myFormattedDate;
>
> ?>
>
> http://www.php.net/strtotime
> http://www.php.net/date
>
> Of the two methods, I prefer doing it in SQL.
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/contact.php

Report · Apr 21, 2008

On 20 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:

> writes December 31, 1969, no matter what the value of "news_date"
> I think I'm close, but no cigar.
>
> Any suggestions?

I personally find it's easier to do in SQL...

What value does $row_get_News['news_date'] have before you go into that
code snippet? Where is it getting it from?

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Apr 21, 2008

$row_get_News['news_date']
is from a MySQL database table named "news"
the table row "news_date" field type is DATE, the format is "yyyy-mm-dd"
so entries are things like "2005-03-17" or "2004-11-02"

I would like to reformat the above to March 17 2005 or November 2 2004

Currently I'm using the "news_date" row to sort the table in decending order
From my web page "news.php" , my SQL is:

mysql_select_db($database_getCrowsey, $getCrowsey);
$query_getNews = "SELECT * FROM news ORDER BY news_date DESC";
$getNews = mysql_query($query_getNews, $getCrowsey) or die(mysql_error());
$row_getNews = mysql_fetch_assoc($getNews);
$totalRows_getNews = mysql_num_rows($getNews);

Jeff

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns9A87485E360FCmakowiecatnycapdotrE@216.104.212.96...
> On 20 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:
>
>> writes December 31, 1969, no matter what the value of "news_date"
>> I think I'm close, but no cigar.
>>
>> Any suggestions?
>
> I personally find it's easier to do in SQL...
>
> What value does $row_get_News['news_date'] have before you go into that
> code snippet? Where is it getting it from?
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/contact.php

Report · Apr 21, 2008

On 21 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:

> $query_getNews = "SELECT * FROM news ORDER BY news_date DESC";

$query_getNews = "SELECT field1, field2,
DATE_FORMAT(news_date, '%M %e, %Y') AS myFormattedDate
FROM news
ORDER BY news_date DESC";

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Apr 21, 2008

I changed the code on news.php to read:

mysql_select_db($database_getCrowsey, $getCrowsey);
$query_getNews = "SELECT field1, field2,
DATE_FORMAT(news_date, '%M %e, %Y') AS myFormattedDate
FROM news
ORDER BY news_date DESC";
$getNews = mysql_query($query_getNews, $getCrowsey) or die(mysql_error());
$row_getNews = mysql_fetch_assoc($getNews);
$totalRows_getNews = mysql_num_rows($getNews);

news.php will not open, I get the error:

Unknown column 'field1' in 'field list'

For general background I'm using:
PHP version 5.2.0
MySQL client API version 5.0.27

Jeff

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns9A875DB2E6BC5makowiecatnycapdotrE@216.104.212.96...
> On 21 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:
>
>> $query_getNews = "SELECT * FROM news ORDER BY news_date DESC";
>
> $query_getNews = "SELECT field1, field2,
> DATE_FORMAT(news_date, '%M %e, %Y') AS myFormattedDate
> FROM news
> ORDER BY news_date DESC";
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/contact.php

Report · Apr 21, 2008

Jeff wrote:
> news.php will not open, I get the error:
>
> Unknown column 'field1' in 'field list'

You need to learn how to read the type of answers you get in a forum
like this.

"field1, field2" means "replace these dummy names with the real names of
your columns (fields)".

--
David Powers, Adobe Community Expert
Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

Report · Apr 21, 2008

Thank you David, I will try to do better.

Jeff

"David Powers" <david@example.com> wrote in message
news:fuicq5$n9t$1@forums.macromedia.com...
> Jeff wrote:
>> news.php will not open, I get the error:
>>
>> Unknown column 'field1' in 'field list'
>
> You need to learn how to read the type of answers you get in a forum like
> this.
>
> "field1, field2" means "replace these dummy names with the real names of
> your columns (fields)".
>
> --
> David Powers, Adobe Community Expert
> Author, "The Essential Guide to Dreamweaver CS3" (friends of ED)
> Author, "PHP Solutions" (friends of ED)
> http://foundationphp.com/

Report · Apr 21, 2008

I did finally get this to work.
Thank you David, a few minutes with my nose in your book "Dreamweaver CS3
with......) and it came clear.
The answer:

$query_getNews = "SELECT *, DATE_FORMAT(news_date, '%M %e, %Y') AS news_date
FROM news ORDER BY news_date DESC";

Jeff

"Jeff" <jeffs@NoSpamLamSam.com> wrote in message
news:fui6ko$hls$1@forums.macromedia.com...
>I changed the code on news.php to read:
>
> mysql_select_db($database_getCrowsey, $getCrowsey);
> $query_getNews = "SELECT field1, field2,
> DATE_FORMAT(news_date, '%M %e, %Y') AS myFormattedDate
> FROM news
> ORDER BY news_date DESC";
> $getNews = mysql_query($query_getNews, $getCrowsey) or die(mysql_error());
> $row_getNews = mysql_fetch_assoc($getNews);
> $totalRows_getNews = mysql_num_rows($getNews);
>
> news.php will not open, I get the error:
>
> Unknown column 'field1' in 'field list'
>
> For general background I'm using:
> PHP version 5.2.0
> MySQL client API version 5.0.27
>
>
> Jeff
>
>
> "Joe Makowiec" <makowiec@invalid.invalid> wrote in message
> news:Xns9A875DB2E6BC5makowiecatnycapdotrE@216.104.212.96...
>> On 21 Apr 2008 in macromedia.dreamweaver.appdev, Jeff wrote:
>>
>>> $query_getNews = "SELECT * FROM news ORDER BY news_date DESC";
>>
>> $query_getNews = "SELECT field1, field2,
>> DATE_FORMAT(news_date, '%M %e, %Y') AS myFormattedDate
>> FROM news
>> ORDER BY news_date DESC";
>>
>> --
>> Joe Makowiec
>> http://makowiec.net/
>> Email: http://makowiec.net/contact.php
>
>