content_image/742483.jpg content_image/742482.jpg
If strict SQL mode is not enabled, MySQL converts invalid YEAR  values to 0000 . In strict SQL mode, attempting to insert an invalid YEAR  value produces an error. 

See also Section 11.2.8, “2-Digit Years in Dates” . 

## 11.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME 

TIMESTAMP  and DATETIME  columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). 

For any TIMESTAMP  or DATETIME  column in a table, you can assign the current timestamp as the default value, the auto-update value, or both: 

•An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column. 

•An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP ). 

In addition, if the explicit_defaults_for_timestamp  system variable is disabled, you can initialize or update any TIMESTAMP  (but not DATETIME ) column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL  attribute to permit NULL  values. 

To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP  and ON UPDATE CURRENT_TIMESTAMP  clauses in column definitions. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for CURRENT_TIMESTAMP  have the same meaning as CURRENT_TIMESTAMP . These are CURRENT_TIMESTAMP() , NOW() , LOCALTIME , LOCALTIME() , LOCALTIMESTAMP , and LOCALTIMESTAMP() . 

Use of DEFAULT CURRENT_TIMESTAMP  and ON UPDATE CURRENT_TIMESTAMP  is specific to TIMESTAMP  and DATETIME . The DEFAULT  clause also can be used to specify a constant (nonautomatic) default value (for example, DEFAULT 0  or DEFAULT ’2000-01-01 00:00:00’ ). 

<img src='content_image/742483.jpg'>

<img src='content_image/742482.jpg'>

## Note 

The following examples use DEFAULT 0 , a default that can produce warnings or errors depending on whether strict SQL mode or the NO_ZERO_DATE  SQL mode is enabled. Be aware that the TRADITIONAL  SQL mode includes strict mode and NO_ZERO_DATE . See Section 5.1.11, “Server SQL Modes” . 

TIMESTAMP  or DATETIME  column definitions can specify the current timestamp for both the default and auto-update values, for one but not the other, or for neither. Different columns can have different combinations of automatic properties. The following rules describe the possibilities: 

•With both DEFAULT CURRENT_TIMESTAMP  and ON UPDATE CURRENT_TIMESTAMP , the column has the current timestamp for its default value and is automatically updated to the current timestamp. 

CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); 

•With a DEFAULT  clause but no ON UPDATE CURRENT_TIMESTAMP  clause, the column has the given default value and is not automatically updated to the current timestamp. 

