Store DateTime value 0001-01-01 in Mysql always NULL

[eric012]

Hello i have an issue when i try to insert a default date value 0001-01-01 from Talend job to my Mysql table i get a NULL value in the table .

There is a way to resolve this problem.

Thanks in advance

[PaulyWally]

The MySQL documentation might give you the answer you are looking for:

https://dev.mysql.com/doc/refman/5.7/en/datetime.html

DATE... The supported range is '1000-01-01' to '9999-12-31'.

DATETIME... The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

I don't have a MySQL install handy to test out, but my suggestion is you should try to stay within the limits of the MySQL data type.

You'll likely get differing opinions on this, but in my personal opinion there is no reason to use a default value. Especially one that would never be valid like 0001-01-01. NULLs are perfectly valid in today's DBMSs, and are handled quite gracefully in modern stacks. I have never heard a good argument for using a "dummy" default value - especially in a date or numeric field.

If you don't have control over the DB schema, and you have some type of requirement to have a default value entered from Talend, please refer to the MySQL docs I linked above. There isn't much you can do if MySQL simply will not accept 0001-01-01 in a date column.