Syntax\n------\n\nDATETIME [(microsecond precision)]\n\nDescription\n-----------\n\nA date and time combination.\n\nMariaDB displays DATETIME values in 'YYYY-MM-DD HH:MM:SS.ffffff' format, but allows assignment of values to DATETIME columns using either strings or numbers. For details, see date and time literals.\n\nDATETIME columns also accept CURRENT_TIMESTAMP as the default value.\n\nThe --mysql56-temporal-format option, on by default, allows MariaDB to store DATETME values using the same low-level format MySQL 5.6 uses. For more information, see Internal Format, below.\n\nFor storage requirements, see Data Type Storage Requirements.\n\nSupported Values\n\nMariaDB stores values that use the DATETIME data type in a format that supports values between 1000-01-01 00:00:00.000000 and 9999-12-31 23:59:59.999999.\n\nMariaDB can also store microseconds with a precision between 0 and 6. If no microsecond precision is specified, then 0 is used by default.\n\nMariaDB also supports '0000-00-00' as a special _zero-date_ value, unless NO_ZERO_DATE is specified in the SQL_MODE. Similarly, individual components of a date can be set to 0 (for example: '2015-00-12'), unless NO_ZERO_IN_DATE is specified in the SQL_MODE. In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL. If the ALLOW_INVALID_DATES SQL_MODE is enabled, if the day part is in the range between 1 and 31, the date does not produce any error, even for months that have less than 31 days.\n\nOracle Mode\n\nIn Oracle mode, DATE with a time portion is a synonym for DATETIME. See also mariadb_schema.\n\nInternal Format\n\nA new temporal format was introduced from MySQL 5.6 that alters how the TIME, DATETIME and TIMESTAMP columns operate at lower levels. These changes allow these temporal data types to have fractional parts and negative values. You can disable this feature using the mysql56_temporal_format system variable.\n\nTables that include TIMESTAMP values that were created on an older version of MariaDB or that were created while the mysql56_temporal_format system variable was disabled continue to store data using the older data type format.\n\nIn order to update table columns from the older format to the newer format, execute an ALTER TABLE... MODIFY COLUMN statement that changes the column to the _same_ data type. This change may be needed if you want to export the table's tablespace and import it onto a server that has mysql56_temporal_format=ON set (see MDEV-15225).\n\nFor instance, if you have a DATETIME column in your table:\n\n``sql\nSHOW VARIABLES LIKE 'mysql56_temporal_format';\n\n+-------------------------+-------+\n| Variable_name           | Value |\n+-------------------------+-------+\n| mysql56_temporal_format | ON    |\n+-------------------------+-------+\n\nALTER TABLE example_table MODIFY ts_col DATETIME;\n`\n\nWhen MariaDB executes the ALTER TABLE statement, it converts the data from the older temporal format to the newer one.\n\nIn the event that you have several tables and columns using temporal data types that you want to switch over to the new format, make sure the system variable is enabled, then perform a dump and restore using mysqldump. The columns using relevant temporal data types are restored using the new temporal format.\n\nColumns with old temporal formats are marked with a / mariadb-5.3 / comment in the output of SHOW CREATE TABLE, SHOW COLUMNS, DESCRIBE statements, as well as in the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS Table.\n\n`sql\nSHOW CREATE TABLE mariadb5312_datetime\\G\n************************ 1. row ***********************\n       Table: mariadb5312_datetime\nCreate Table: CREATE TABLE mariadb5312_datetime (\n  dt0 datetime / mariadb-5.3 / DEFAULT NULL,\n  dt6 datetime(6) / mariadb-5.3 / DEFAULT NULL\n) ENGINE=MyISAM DEFAULT CHARSET=latin1\n``\n\nExamples\n--------\n\nCREATE TABLE t1 (d DATETIME);\n\nINSERT INTO t1 VALUES ("2011-03-11"), ("2012-04-19 13:08:22"),\n ("2013-07-18 13:44:22.123456");\n\nSELECT  FROM t1;\n+---------------------+\n| d                   |\n+---------------------+\n| 2011-03-11 00:00:00 |\n| 2012-04-19 13:08:22 |\n| 2013-07-18 13:44:22 |\n+---------------------+\n\nURL: https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime