Jump to content

How do I convert this date?


Jeffro

Recommended Posts

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.

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') )";

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"

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() );

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.... 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.