Date conversion

[Courtman]

I am having a problem with converting a date in MySQL. MySQL stores it as 2004-06-30 and I want to display it as 30 Jun 2004. How can I convert the MySQL string into the output I like?

To further complicate this, I also would like to add 6 months to the outputted date! Is this easy or impossible?

Thanks!

[coopster]

We forgot to Welcome you to WebmasterWorld the last time around, Courtman! Welcome!

You could simple modify the the DATE_FORMAT [dev.mysql.com].

SELECT DATE_FORMAT('2004-06-30', '%d %b %Y');

And to add 6 months, use the DATE_ADD function:

SELECT DATE_FORMAT(DATE_ADD('2004-06-30', INTERVAL 6 MONTH), '%d %b %Y');

[Courtman]

So, my code would look something like this:

<?php
SELECT DATE_FORMAT(DATE_ADD($a_row[lastchk], INTERVAL 6 MONTH), '%d %b %Y');
?>

where lastchck is the field in my MySQL table...?

[Birdman]

It's not too hard, really.

First, I should mention that, you can alter the format of a date field when selecting it from the db.

SELECT DATE_FORMAT [dev.mysql.com](date_field_name ,'%e %b % %Y') as mydate FROM mytable WHERE...

That way the date is already formatted when you get it. Now, to add six months, this may work:

SELECT ADDTIME(DATE_FORMAT [dev.mysql.com](date_field_name ,'%e %b % %Y'), '0 6:0:0.000000') as mydate FROM mytable WHERE...

Anyhow, see the link above for way too much mysql date info ;)

As for a PHP solution, try this:

$date_array = explode("-", $date);
$formatted_date = date [php.net]("j M Y", mktime [php.net](0, 0, 0, $date_array[1], $date_array[2], $date_array[0]));

Too quick for me coopster!

[coopster]

The format you are showing, Courtman is after the query has been run and the resulting set of data has been retrieved from the table. The MySQL options shown here will format the date prior to the query execution and should be included in your query statement, something like

SELECT DATE_FORMAT(DATE_ADD(lastchk, INTERVAL 6 MONTH), '%d %b %Y');

If you want to format it after running the query, have a look at the technique Birdman has shown.

[Courtman]

Thanks for your replies to this topic chaps. Why is it that every time I make a mental breakthrough with PHP I hit another wall?!

I use the following query:

$result = mysql_query( "SELECT * FROM engineering ORDER BY reg" );
while ( $a_row = mysql_fetch_array( $result ) )
{
<?=$a_row[reg]?> ... and so on thru my table.
}

The lastchk field and one other I want to format dd-Mmm-yy and add 6 months and 12 months to respectively. Is the easiest way to do this by altering the way I query the table or to write some php where I want to display the result...?

[Courtman]

Coopster, Birdman

Thanks for your help - I've sorted it out using Birdman's PHP and adding 6 to the month array. The easy solutions seem to stare me in the face and laugh at me for at least a week before I suss them out!