第1个回答 2011-01-19
mysql> SELECT 'NOW' DisplayMode, NOW() B
-> UNION ALL
-> SELECT 'Second' DisplayMode, Second(NOW()) B
-> UNION ALL
-> SELECT 'Minute' DisplayMode, MINUTE(NOW()) B
-> UNION ALL
-> SELECT 'Hour' DisplayMode, Hour(NOW()) B
-> UNION ALL
-> SELECT 'Day' DisplayMode, DAY(NOW()) B
-> UNION ALL
-> SELECT 'Week' DisplayMode, Week(NOW()) B
-> UNION ALL
-> SELECT 'Month' DisplayMode, Month(NOW()) B
-> UNION ALL
-> SELECT 'Year' DisplayMode, Year(NOW()) B;
+-------------+---------------------+
| DisplayMode | B |
+-------------+---------------------+
| NOW | 2010-10-22 20:43:09 |
| Second | 9 |
| Minute | 43 |
| Hour | 20 |
| Day | 22 |
| Week | 42 |
| Month | 10 |
| Year | 2010 |
+-------------+---------------------+
8 rows in set (0.00 sec)
上面这个 SQL 是 2010-10-22 20:43:09 执行的, 用于测试 获取一个日期里面的各个组成部分的例子。年月日时分秒都有。
第2个回答 2011-01-19
把要获取的日期转成字符型的,然后按位接取就得到了,这是一种方法,也是最省事的方法.
第3个回答 推荐于2017-11-23
分别用YEAR,MONTH,DAY函数,例如
mysql> select * from test;
+------------+
| DATE |
+------------+
| 2011-01-19 |
| 2011-01-19 |
| 2010-06-01 |
| 2010-06-10 |
| 2010-07-10 |
+------------+
年
mysql> select YEAR(DATE) from test;
+------------+
| YEAR(DATE) |
+------------+
| 2011 |
| 2011 |
| 2010 |
| 2010 |
| 2010 |
+------------+
月:
mysql> select month(DATE) from test ;
+-------------+
| month(DATE) |
+-------------+
| 1 |
| 1 |
| 6 |
| 6 |
| 7 |
+-------------+
日:
mysql> select day(DATE) from test;
+-----------+
| day(DATE) |
+-----------+
| 19 |
| 19 |
| 1 |
| 10 |
| 10 |
+-----------+本回答被提问者采纳
第4个回答 2011-01-19
YEAR(serial_number) = YEAR YEAR("2001/9/10") =2001
MONTH(serial_number) = MONTH MONTH ("2001/9/10") = 9
DAY(serial_number) = DAY DAY("2001/9/10") = 10