Menu
I have a table with a DATETIME column. I would like to SELECT this datetime value and INSERT it into another column.
I did this (note: '2011-12-18 13:17:17' is the value the former SELECT gave me from the DATETIME field):
UPDATE products SET former_date=2011-12-18 13:17:17 WHERE id=1 and get
1064 - 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 '13:17:17 WHERE itemid=1' at line 1 Ok, I understand it's wrong to put an unquoted string in there, but is DATETIME just a string in the first place? What do I put in there? All I want is reliably transfer the existing value over to a new datetime field...
EDIT:
The reason I ask is: I have this special definition, DATETIME, and somehow I thought it gives me some security and other advantages when handling dates. Now it seems it is simply a specialized VARCHAR, so to speak.
Thanks for your answers, it seems this is indeed the intended behaviour.
467
You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function. Let us set the default value with some date.
Example: For converting datetime to format – dd:mm:yyyy, you can use the following query: SELECT DATE_FORMAT('2020-03-15 07:10:56.123', '%d:%m:%Y');
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format.
According to MySQL documentation, you should be able to just enclose that datetime string in single quotes, ('YYYY-MM-DD HH:MM:SS') and it should work. Look here: Date and Time Literals
So, in your case, the command should be as follows:
UPDATE products SET former_date='2011-12-18 13:17:17' WHERE id=1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
