mysql的字符串和日期类型的转换。
1.now()和curdate()的区别:
now():datetime类型。
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-10-27 21:14:40 |
+---------------------+
1 row in set (0.01 sec)
curdate():date类型
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2013-10-27 |
+------------+
1 row in set (0.00 sec)
2.日期-字符串类型的转换。
<1>字符串 -> 日期:
str_to_date(日期字符串,'模式字符串')
例子:student(id,name,score,birth);
update student set birth=str_to_date('1985-01-04','%Y-%m-%d');
<2>日期 -> 字符串:
date_format(birth,'%Y/%m/%d');
alter table student add column birth_str varchar(20);
update student set birth_str = date_format(birth,'%Y/%m/%d');
mysql> select * from student; +----+--------+-------+------------+------------+ | id | name | score | birth | birth_str | +----+--------+-------+------------+------------+ | 1 | Aaron | 78.00 | 1985-01-04 | 1985/01/04 |
(关于模式字符串的简单说明)
%Y=============2013
%y=============13
%m=============01
%M=============January
%d=============04
%D=============4th
%h:%i:%s=======时:分:秒