How do I convert this date?

[Jeffro]

A php request.... 

 

I need to convert this:

 

Tue, 21 Jun 2011 03:38:00 GMT

 

to this format:

 

06/21/2011

[Pikachu2000]

echo date('m/d/Y', strtotime('Tue, 21 Jun 2011 03:38:00 GMT'));

[Jeffro]

It's actually for a mysql database insert into a date column with a type of 'timestamp', which is currently displaying as:  0000-00-00 00:00:00

 

Is there a way to convert it to this?  I'd like to keep the timestamp type if possible.. as I want to sort by it in the end.

[Pikachu2000]

That's not what you said in your first post. Are you trying to insert it into the db, or what? Where is the value coming from to begin with?

[Jeffro]

yep.. trying to insert into mysql.  The value is coming from an xml page.  The insert of everything else is working..  I just can't seem to make the date convert.

[Pikachu2000]

Your best bet is probably going to be using MySQL's STR_TO_DATE() function. This should be what you need.

 

$date_string= 'Tue, 21 Jun 2011 03:38:00';
$query = "INSERT INTO table (field) VALUES ( STR_TO_DATE('$date_string','%a, %d %M %Y %H:%i:%s') )";

[Jeffro]

Your best bet is probably going to be using MySQL's STR_TO_DATE() function. This should be what you need.

 

$date_string= 'Tue, 21 Jun 2011 03:38:00';
$query = "INSERT INTO table (field) VALUES ( STR_TO_DATE('$date_string','%a, %d %M %Y %H:%i:%s') )";

 

I can't seem to get my concantenation right. 

Any idea what I'm doing wrong? 

mysql_query("INSERT IGNORE INTO mytable
(url, title, description, date, place) VALUES('".$link."', '".$title."', '".$description."', '"STR_TO_DATE('$date','%a, %d %M %Y %H:%i:%s')"', 'website' ) ") 
or die(mysql_error()); 

 

It works when using date NOW .. but when trying the above I keep getting,

"Tue, 21 Jun 2011 03:38:00 GMTYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Tue, 21 Jun 2011 03:38:00 GMT','%a, %d %M %Y %H:%i:%s')', 'website' )' at line 2"

[Pikachu2000]

You don't need all that concatenation. It leads to errors and makes it harder to read the code. It's also better practice to form a query string in a variable, and use the variable in the query execution. Then you can echo the entire query string if there are errors.

 

$query = "INSERT IGNORE INTO mytable (url, title, description, date, place) VALUES ( '$link', '$title', '$description', STR_TO_DATE('$date', '%a, %d %M %Y %H:%i:%s'), 'website' )";
mysql_query($query) or die( "<br>Query string: $query<br>Caused error: " . mysql_error() );

[Jeffro]

You don't need all that concatenation. It leads to errors and makes it harder to read the code. It's also better practice to for a query string in a variable, and use the variable in the query execution. Then you can echo the entire query string if there are errors.

 

$query = "INSERT IGNORE INTO mytable (url, title, description, date, place) VALUES ( '$link', '$title', '$description', STR_TO_DATE('$date', '%a, %d %M %Y %H:%i:%s'), 'website' )";
mysql_query($query) or die( "<br>Query string: $query<br>Caused error: " . mysql_error() );

 

It works!! Hooray!  Thanks for sticking with me on that.   

...and an equally sincere thanks for the much easier mysql insert line! 

 

Much much appreciated....