Insert string to timestamp fails

Question

I have a table

CREATE TEMPORARY TABLE test (stamp TIMESTAMP);

And try to run inserts on it

INSERT INTO test VALUES('2003-01-01')

Fails with Incorrect datetime value: '2003-01-01' for column 'stamp' at row 1

And this one works

INSERT INTO test VALUES('2004-01-01')

Why would that happen?

score 1 · Answer 1 · answered Nov 12 '14 at 14:18

1

You should not use TIMESTAMP:

CREATE TEMPORARY TABLE `test` (`stamp` DATETIME);

You need to give that in the right format:

INSERT INTO `test` VALUES ('2004-01-01 00:00:00')

answered Nov 12 '14 at 14:18

That one works. But INSERT INTO `test` VALUES ('2003-01-01 0:00:00') still fails – clorz Nov 12 '14 at 14:19
@clorz Check this out! – Praveen Kumar Purushothaman Nov 12 '14 at 14:19
`0:00:00` is not valid. it's `hh:mm:ss`, not `h:mm:ss`. the zero-padding counts. – Marc B Nov 12 '14 at 14:19
INSERT INTO `test` VALUES ('2003-01-01 00:00:00') also fails – clorz Nov 12 '14 at 14:20
@clorz Did you try creating it as a `DATETIME`? – Praveen Kumar Purushothaman Nov 12 '14 at 14:20
@clorz `TIMESTAMP` is related to `UPDATE` function. – Praveen Kumar Purushothaman Nov 12 '14 at 14:22
@clorz Check this out: [datetime vs timestamp](http://stackoverflow.com/questions/409286/datetime-vs-timestamp) – Praveen Kumar Purushothaman Nov 12 '14 at 14:23
Thank you. Yes, I'll use datetime instead. But this still seem puzzling. As if that particular hour did not exist. Even INSERT INTO `test` VALUES ('2003-01-01 01:00:00') works, but anything below 01:00:00 does not. – clorz Nov 12 '14 at 14:28

1 Answers1