[SOLVED] Change mysql date format.

[defeated]

Hi,

When I output a date from mysql into a html page how do I change the format from 2008-03-14 to 14-03-2008 in the display?

 

Also if it is timedate in the db how do I do the above and also delete the time part in my output?

[paul2463]

have  a look here at the <A href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format"> MySql Manual </a> at formatting the date as you pull it from the database

[fenway]

DATE_FORMAT( yourDateTimeField, '%d-%m-%Y )

[defeated]

So thats...

 

<?php echo DATE_FORMAT( yourDateTimeField, '%d-%m-%Y ) ; ?>

 

Is that right?

[PFMaBiSmAd]

DATE_FORMAT() is a mysql function. You would use it in a query statement.

[defeated]

See that's where I come unstuck.

It doesn't go......

$result=mysql_query("SELECT * FROM mytable WHERE id=12")or die(mysql_error());
$row=mysql_fetch_array($result);
$mytimedatefield=$row["DATE_FORMAT( mytimedatefield, '%d-%m-%Y )"] ;
echo $mytimedatefield ; 

 

But you get the idea of what I'm trying to do here.

[paul2463]

try this

<?php
$date = "2008-03-14";
$pieces = explode("-",$date);
$newdate = "$pieces[2]-$pieces[1]-$pieces[0]";
echo $newdate;//prints out 14-03-2008
?>

[defeated]

Thats done it nicely Paul. Many thanks.

Ian

[fenway]

No, it goes:

 

$result=mysql_query("SELECT *, DATE_FORMAT( mytimedatefield, '%d-%m-%Y ) as pretty FROM mytable WHERE id=12")or die(mysql_error());
$row=mysql_fetch_array($result);
$mytimedatefield=$row["pretty"] ;
echo $mytimedatefield ; 

[defeated]

I used Pauls solution.  But now I have found myself in a situation where Fenways would work better.  Only problem is that I have 3 date fields to be extracted and formatted per row.  So Fenway, how do you write your statement with mytimedatefield1, mytimedatefield2, mytimedatefield3?  I'm too embarrassed to show you what I tried!!!!

lol

[paul2463]

$result=mysql_query("SELECT *, DATE_FORMAT( mytimedatefield1, '%d-%m-%Y ) as pretty1, DATE_FORMAT( mytimedatefield2, '%d-%m-%Y ) as pretty2, DATE_FORMAT( mytimedatefield3, '%d-%m-%Y ) as pretty3 FROM mytable WHERE id=12")or die(mysql_error());
$row=mysql_fetch_assoc($result);
$mytimedatefield1=$row["pretty1"] ;
$mytimedatefield2=$row["pretty2"] ;
$mytimedatefield3=$row["pretty3"] ;
echo "$mytimedatefield1 - $mytimedatefield2 - $mytimedatefield3" ;

[defeated]

Clearly Paul wasn't too embarrassed! 

That's what I tried. Didn't work. My line at present is

$result=mysql_query("SELECT *, DATE_FORMAT(mytimedatefield1,'%d-%m-%Y') AS pretty1 FROM mytable WHERE TO_DAYS(NOW()) - TO_DAYS(mytimedatefield3) = 0") or die(mysql_error());

note the extra ' after %Y. It also didn't seem to like spaces. Still need to get mytimedatefield2 and mytimedatefield3 formatted in the same line though.

 

P.S. Thanks for the help so far.

[paul2463]

what was the error????

[defeated]

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 'pretty2 FROM mytable WHERE TO_DAYS(NOW()) - TO_DAYS(mytimedatefield3) = 0' at line 1

[fenway]

This should be fine:

SELECT *, DATE_FORMAT( mytimedatefield1, '%d-%m-%Y' ) as pretty1, DATE_FORMAT( mytimedatefield2, '%d-%m-%Y' ) as pretty2, DATE_FORMAT( mytimedatefield3, '%d-%m-%Y' ) as pretty3 FROM mytable WHERE id=12")

[mkoga]

Although I prefer to format on Mysql side of things. Here is a clean PHP solution:

 

<?php
echo date('d-m-Y', strtotime('2008-03-14')); //prints out 14-03-2008
?>

[defeated]

That's neat.  Nice and short. Going to bang away at the sql solution but can't get the ddamn thing to work.  It's not really very important since it's only for an email to tell our consultants when a job has expired on the site.... but it's anoying me now so I WILL get it going.!!

[defeated]

Fenway, I just got it to work.... I did something really stupid... put in the wrong field name for one of my date fields!

How much after the original post is this?? Still.... all's well that ends well. 

thanks for your help once again.... even if it's a little bit belated this time.

Ian.