关于日期处理的实例:

从mysql给出的 example 这个是官方源码下载以及导入,http://dev.mysql.com/doc/employee/en/employees-installation.html

然后执行下面的操作:

mysql> create table employees like employees.employees;<br/>
Query OK, 0 rows affected (0.11 sec)

mysql> desc employees;<br/>
+------------+---------------+------+-----+---------+-------+<br/>
| Field      | Type          | Null | Key | Default | Extra |<br/>
+------------+---------------+------+-----+---------+-------+<br/>
| emp_no     | int(11)       | NO   | PRI | NULL    |       |<br/>
| birth_date | date          | NO   |     | NULL    |       |<br/>
| first_name | varchar(14)   | NO   |     | NULL    |       |<br/>
| last_name  | varchar(16)   | NO   |     | NULL    |       |<br/>
| gender     | enum('M','F') | NO   |     | NULL    |       |<br/>
| hire_date  | date          | NO   |     | NULL    |       |<br/>
+------------+---------------+------+-----+---------+-------+<br/>
6 rows in set (0.00 sec)<br/>
从其他数据库中指定的表中导入数据,employees.employees 导入前10条数据<br/>
mysql> insert into employees select * from employees.employees limit 10;<br/>
Query OK, 10 rows affected (0.04 sec)<br/>
Records: 10  Duplicates: 0  Warnings: 0<br/>
仅查询employess表中的last_name,first_name和birthd_date三个数据<br/>
mysql> select concat(last_name,' ',first_name) as name,birth_date as birthday from employees;<br/>
mysql> select concat(last_name,' ',first_name) as name,birth_date as birthday from employees;<br/>
+--------------------+------------+<br/>
| name               | birthday   |<br/>
+--------------------+------------+<br/>
| Facello Georgi     | 1953-09-02 |<br/>
| Simmel Bezalel     | 1964-06-02 |<br/>
| Bamford Parto      | 1959-12-03 |<br/>
| Koblick Chirstian  | 1954-05-01 |<br/>
| Maliniak Kyoichi   | 1955-01-21 |<br/>
| Preusig Anneke     | 1953-04-20 |<br/>
| Zielinski Tzvetan  | 1957-05-23 |<br/>
| Kalloufi Saniya    | 1958-02-19 |<br/>
| Peac Sumant        | 1952-04-19 |<br/>
| Piveteau Duangkaew | 1963-06-01 |<br/>
| amos li            | 1972-02-29 |<br/>
+--------------------+------------+<br/>
11 rows in set (0.00 sec)

然后执行下面的命令:用来计算每位员工的出生日期与当前日期相差的年份,以及当前的日期.

select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees;
+-------------------+------------+------+---------------------+<br/>
| name              | birthday   | diff | today               |<br/>
+-------------------+------------+------+---------------------+<br/>
| FacelloGeorgi     | 1953-09-02 |   60 | 2013-12-08 02:12:54 |<br/>
| SimmelBezalel     | 1964-06-02 |   49 | 2013-12-08 02:12:54 |<br/>
| BamfordParto      | 1959-12-03 |   54 | 2013-12-08 02:12:54 |<br/>
| KoblickChirstian  | 1954-05-01 |   59 | 2013-12-08 02:12:54 |<br/>
| MaliniakKyoichi   | 1955-01-21 |   58 | 2013-12-08 02:12:54 |<br/>
| PreusigAnneke     | 1953-04-20 |   60 | 2013-12-08 02:12:54 |<br/>
| ZielinskiTzvetan  | 1957-05-23 |   56 | 2013-12-08 02:12:54 |<br/>
| KalloufiSaniya    | 1958-02-19 |   55 | 2013-12-08 02:12:54 |<br/>
| PeacSumant        | 1952-04-19 |   61 | 2013-12-08 02:12:54 |<br/>
| PiveteauDuangkaew | 1963-06-01 |   50 | 2013-12-08 02:12:54 |<br/>
| amosli            | 1972-02-29 |   41 | 2013-12-08 02:12:54 |<br/>
+-------------------+------------+------+---------------------+<br/>
11 rows in set (0.00 sec)

接下来是为了计算今年和明年的生日,注意如果生日为2月29日,且目标日期不是闰月,那么这两列所包含的将是2月28日,而非3月1日.

select name,birthday,today,date_add(birthday,interval diff year) as cur,date_add(birthday,interval diff+1 year ) as next from (select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees) as a;
+-------------------+------------+---------------------+------------+------------+<br/>
| name              | birthday   | today               | cur        | next       |<br/>
+-------------------+------------+---------------------+------------+------------+<br/>
| FacelloGeorgi     | 1953-09-02 | 2013-12-08 02:14:27 | 2013-09-02 | 2014-09-02 |<br/>
| SimmelBezalel     | 1964-06-02 | 2013-12-08 02:14:27 | 2013-06-02 | 2014-06-02 |<br/>
| BamfordParto      | 1959-12-03 | 2013-12-08 02:14:27 | 2013-12-03 | 2014-12-03 |<br/>
| KoblickChirstian  | 1954-05-01 | 2013-12-08 02:14:27 | 2013-05-01 | 2014-05-01 |<br/>
| MaliniakKyoichi   | 1955-01-21 | 2013-12-08 02:14:27 | 2013-01-21 | 2014-01-21 |<br/>
| PreusigAnneke     | 1953-04-20 | 2013-12-08 02:14:27 | 2013-04-20 | 2014-04-20 |<br/>
| ZielinskiTzvetan  | 1957-05-23 | 2013-12-08 02:14:27 | 2013-05-23 | 2014-05-23 |<br/>
| KalloufiSaniya    | 1958-02-19 | 2013-12-08 02:14:27 | 2013-02-19 | 2014-02-19 |<br/>
| PeacSumant        | 1952-04-19 | 2013-12-08 02:14:27 | 2013-04-19 | 2014-04-19 |<br/>
| PiveteauDuangkaew | 1963-06-01 | 2013-12-08 02:14:27 | 2013-06-01 | 2014-06-01 |<br/>
| amosli            | 1972-02-29 | 2013-12-08 02:14:27 | 2013-02-28 | 2014-02-28 |<br/>
+-------------------+------------+---------------------+------------+------------+<br/>
11 rows in set (0.00 sec)

如果出生的日期是闰月,并且当前的年份不是闰年,那么日期加一,表示3月1日为生日,对于下一个年份使用同样的操作:

 select name,birthday,date_add(next,interval if(day(birthday)=29&&day(next)=28,1,0) day) as next,today,date_add(cur,interval if(day(birthday)=29&&day(cur)=28,1,0) day) as cur from(select name,birthday,today,date_add(birthday,interval diff year) as cur,date_add(birthday,interval diff+1 year ) as next from (select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees) as a) as b; 
+-------------------+------------+------------+---------------------+------------+<br/>
| name              | birthday   | next       | today               | cur        |<br/>
+-------------------+------------+------------+---------------------+------------+<br/>
| FacelloGeorgi     | 1953-09-02 | 2014-09-02 | 2013-12-08 02:19:07 | 2013-09-02 |<br/>
| SimmelBezalel     | 1964-06-02 | 2014-06-02 | 2013-12-08 02:19:07 | 2013-06-02 |<br/>
| BamfordParto      | 1959-12-03 | 2014-12-03 | 2013-12-08 02:19:07 | 2013-12-03 |<br/>
| KoblickChirstian  | 1954-05-01 | 2014-05-01 | 2013-12-08 02:19:07 | 2013-05-01 |<br/>
| MaliniakKyoichi   | 1955-01-21 | 2014-01-21 | 2013-12-08 02:19:07 | 2013-01-21 |<br/>
| PreusigAnneke     | 1953-04-20 | 2014-04-20 | 2013-12-08 02:19:07 | 2013-04-20 |<br/>
| ZielinskiTzvetan  | 1957-05-23 | 2014-05-23 | 2013-12-08 02:19:07 | 2013-05-23 |<br/>
| KalloufiSaniya    | 1958-02-19 | 2014-02-19 | 2013-12-08 02:19:07 | 2013-02-19 |<br/>
| PeacSumant        | 1952-04-19 | 2014-04-19 | 2013-12-08 02:19:07 | 2013-04-19 |<br/>
| PiveteauDuangkaew | 1963-06-01 | 2014-06-01 | 2013-12-08 02:19:07 | 2013-06-01 |<br/>
| amosli            | 1972-02-29 | 2014-03-01 | 2013-12-08 02:19:07 | 2013-03-01 |<br/>
+-------------------+------------+------------+---------------------+------------+<br/>
11 rows in set (0.00 sec)

最后判断今年的生日是否已经过了,如果是,那么返回下一年的生日,最后得到的查询结果如下所示:

 select name,birthday,if(cur>today,cur,next) as birth_day from(select name,birthday,date_add(next,interval if(day(birthday)=29&&day(next)=28,1,0) day) as next,today,date_add(cur,interval if(day(birthday)=29&&day(cur)=28,1,0) day) as cur from(select name,birthday,today,date_add(birthday,interval diff year) as cur,date_add(birthday,interval diff+1 year ) as next from (select concat(last_name,first_name) as name,birth_date as birthday,(YEAR(now())-YEAR(birth_date)) as diff, now() as today from employees) as a) as b) as c;
+-------------------+------------+------------+<br/>
| name              | birthday   | birth_day  |<br/>
+-------------------+------------+------------+<br/>
| FacelloGeorgi     | 1953-09-02 | 2014-09-02 |<br/>
| SimmelBezalel     | 1964-06-02 | 2014-06-02 |<br/>
| BamfordParto      | 1959-12-03 | 2014-12-03 |<br/>
| KoblickChirstian  | 1954-05-01 | 2014-05-01 |<br/>
| MaliniakKyoichi   | 1955-01-21 | 2014-01-21 |<br/>
| PreusigAnneke     | 1953-04-20 | 2014-04-20 |<br/>
| ZielinskiTzvetan  | 1957-05-23 | 2014-05-23 |<br/>
| KalloufiSaniya    | 1958-02-19 | 2014-02-19 |<br/>
| PeacSumant        | 1952-04-19 | 2014-04-19 |<br/>
| PiveteauDuangkaew | 1963-06-01 | 2014-06-01 |<br/>
| amosli            | 1972-02-29 | 2014-03-01 |<br/>
+-------------------+------------+------------+<br/>
11 rows in set (0.00 sec)