Mysql日付-文字列変換.

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=======時:分:秒