Strange MySQL warning 1264 for valid DateTime value

Question

I have manually (GASP!) entered a MySQL command at the command line, and I received a Warning which I cannot even begin to understand. (And before anyone says anything, yes, I KNOW: 1. Using the command line interface is not the best approach; 2. My table is NOT named "TABLE_NAME" and my column is NOT named "DateColumn" and my RecordID value is NOT really "1234"; 3. Maybe my column type should be TIMESTAMP, but for now, it's not. Moving on....)

Attempting to enter a value for the date "July 26th, 2012 at 2:27 PM (GMT)", I keyed:

mysql> update TABLE_NAME set DateColumn="2012-07-26 14:27:00" where RecordID="1234";

I received:

Query OK, 1 row affected, 1 warning (0.11 sec) 
Rows matched: 1  Changed: 1  Warnings: 1

So, I keyed:

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'DateColumn' at row 1 |
+---------+------+-----------------------------------------------------+

Weird, I thought. So I checked the table first to confirm the column (field) type:

mysql> describe TABLE_NAME;

+------------+----------+------+-----+-------------------+-------+
| Field      | Type     | Null | Key | Default           | Extra |
| DateColumn | datetime | YES  |     | NULL              |       |
+------------+----------+------+-----+-------------------+-------+

BUT the value DOES get written properly to the database, and not truncated, AFAIK:

mysql> select * from TABLE_NAME where RecordID="1234";

+-----------------------------------------------+
| RecordID | Date_Column         | BlahBlahBlah |
+----------+---------------------+--------------+
|     1234 | 2012-07-26 14:27:00 | something..  | 
+----------+---------------------+--------------+

I've already searched StackOverflow.com for a solution. I've already Googled for an explanation. I've already read up at http://dev.mysql.com/doc/refman/5.5/en/datetime.html where it says:

    MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 

I even had a slight suspicion that it had something to do with the date or time at which I was making the entry; so I will state that the server on which the database is located is on Pacific Daylight Time (GMT-8, except right now GMT-7 for DST); I am logged in (SSH) from a client on EDT (which should not matter); and I am storing all Date_Column values as GMT. At the time I was entering the value "2012-07-26 14:27:00" all three dates were well AFTER that, on 7/30/12. Not that it should matter -- I should be able to enter future dates without getting an error -- but thought it might be helpful for you to know. So --

WHY, OH WHY is "2012-07-26 14:27:00" an Out-of-Range Value?

My MySQL client API version is 5.1.49.

This is the first time I've ever posted on StackOverflow. Thank you in advance for your suggestions.

Cementum

"The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'." So I don't see a problem with your value... – Ponder

What version of MySQL are you running? – Leggy

@Leggy mysql> select version(); version() 5.1.56-log – Cementum

i'm not 100% sure if this is right, but you may want to look into "str_to_date" mysql command. I was looking for a solution last night and was unable to finish it. let me know if "str_to_date" works for you. – Leggy

@Throdne: The solution that Markus suggested (below) seems to work, but so does yours: mysql> update TABLE_NAME set DateColumn=STR_TO_DATE("2012-07-26 14:27:00","%Y-%m-%d %H:%i:%s") where RecordID="1234"; Query OK, 1 row affected (0.34 sec) Rows matched: 1 Changed: 1 Warnings: 0 Notice, however, the exactness with which I had to specify the parameters in the STR_TO_DATE function (%H, not %h, etc.) I found a lot of help on that item at http://dev.mysql.com/...#function_get-format. – Cementum

Answer 1

I wonder if it is converting it to some date format from string. That dateformat would then have too much precision which would be truncated. Try casting it to a datetime before assigning.

Buckingham

Thank you, I will try that. Meanwhile: The Warning didn't say anything about truncating, only that the value was "out of range". (Stumped.) – Cementum

THIS WORKED -- NO WARNINGS! mysql> update TABLE_NAME set DateColumn=CAST("2012-07-26 14:27:00" AS DATETIME) where RecordID="1234"; Query OK, 1 rows affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 I still don't understand why it mattered, why the "precision" differed. But Thank You @Markus – Cementum