DATE型に以下のように、2文字だけしか年を指定しないでINSERTした場合
MYSQLは自動的に

    1970-01-01 ~ 2069-12-31

の間の日付に変換する。
4桁で年を指定すればそのままINSERTされます。

以下検証


mysql> CREATE TABLE `T3` (
-> `BIRTH` DATE NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.18 sec)

mysql> desc T3;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| BIRTH | date | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO T3 VALUES ('10-02-08');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T3 VALUES ('69-12-31');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO T3 VALUES ('70-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO T3 VALUES ('1910-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T3 VALUES ('2070-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
| 1910-01-01 |
| 2070-01-01 |
+------------+
5 rows in set (0.00 sec)

mysql>

date

======= SQLのモードによっても結果が異なる事があります。 ========

mysql> INSERT INTO T3 VALUES ('12:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
| 1910-01-01 |
| 2070-01-01 |
| 2012-00-00 |
+------------+
6 rows in set (0.01 sec)

mysql> set sql_mode ='NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO T3 VALUES ('12:00:00');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'BIRTH' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from T3;
+------------+
| BIRTH |
+------------+
| 2010-02-08 |
| 2069-12-31 |
| 1970-01-01 |
| 1910-01-01 |
| 2070-01-01 |
| 2012-00-00 |
| 0000-00-00 |
+------------+
7 rows in set (0.00 sec)

mysql> set sql_mode ='';
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO T3 VALUES ('12:00:00');
Query OK, 1 row affected (0.02 sec)

mysql>

Comments are closed.

Post Navigation