mysql基础日期处理(六)

来源：互联网发布：知乎招聘编辑：程序博客网时间：2024/06/06 01:34

1:日期类型：（mysql默认的日期类型:yyyy-MM-dd,会自动将字符串类型转化为date类型）

desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int(4) | NO | PRI | NULL | |
| ENAME | varchar(10) | YES | | NULL | |
| JOB | varchar(9) | YES | | NULL | |
| MGR | int(4) | YES | | NULL | |
| HIREDATE | date | YES | | NULL | |
| SAL | double(7,2) | YES | | NULL | |
| COMM | double(7,2) | YES | | NULL | |
| DEPTNO | int(2) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.04 sec)

mysql> select ename,hiredate from emp where hiredate='2017-05-13';
+---------+------------+
| ename | hiredate |
+---------+------------+
| SMITH | 2017-05-13 |
| ALLEN | 2017-05-13 |
| WARD | 2017-05-13 |
| JONES | 2017-05-13 |
| MARTIM | 2017-05-13 |
| BLAKE | 2017-05-13 |
| CLARK | 2017-05-13 |
| SCOLL | 2017-05-13 |
| KING | 2017-05-13 |
| IUSRNER | 2017-05-13 |
| ADAMS | 2017-05-13 |
| JAMES | 2017-05-13 |
| FORD | 2017-05-13 |
| MILLER | 2017-05-13 |
+---------+------------+
14 rows in set (0.00 sec)

2：日期类型处理，mysql数据库有str_to_date将字符串转化为日期的函数

select ename,hiredate from emp where hiredate=str_to_date('2017-05-13','%Y-%m-%d');
+---------+------------+
| ename | hiredate |
+---------+------------+
| SMITH | 2017-05-13 |
| ALLEN | 2017-05-13 |
| WARD | 2017-05-13 |
| JONES | 2017-05-13 |
| MARTIM | 2017-05-13 |
| BLAKE | 2017-05-13 |
| CLARK | 2017-05-13 |
| SCOLL | 2017-05-13 |
| KING | 2017-05-13 |
| IUSRNER | 2017-05-13 |
| ADAMS | 2017-05-13 |
| JAMES | 2017-05-13 |
| FORD | 2017-05-13 |
| MILLER | 2017-05-13 |
+---------+------------+
14 rows in set (0.05 sec)

0 0