mysql 日期类型

mysql> create table testdate(
         -> id int not null auto_increment primary key,
         -> time date);
Query OK, 0 rows affected (0.30 sec)

mysql> insert into testdate(time) values('2007-5-4');
Query OK, 1 row affected (0.06 sec)

mysql> select * from testdate;
+----+------------+
| id | time            |
+----+------------+
| 1       | 2007-05-04 |
+----+------------+
1 row in set (0.00 sec)

mysql> alter table testdate add column current time;
Query OK, 1 row affected (0.25 sec)
Records: 1      Duplicates: 0      Warnings: 0

mysql> update testdate set current='21:18:00' where id=1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1      Changed: 1      Warnings: 0

mysql> select * from testdate;
+----+------------+----------+
| id | time           | current      |
+----+------------+----------+
| 1      | 2007-05-04 | 21:18:00 |
+----+------------+----------+
1 row in set (0.00 sec)

mysql> alter table testdate add column combine timestamp;
Query OK, 1 row affected (0.14 sec)
Records: 1     Duplicates: 0     Warnings: 0

mysql> update testdate set combine='20050504212000' where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1     Changed: 0     Warnings: 0

mysql> select * from testdate;
+----+------------+----------+---------------------+
| id | time          | current     | combine                |
+----+------------+----------+---------------------+
| 1     | 2007-05-04 | 21:18:00 | 2005-05-04 21:20:00 |
+----+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> update testdate set combine='2005-5-4 21:22:0' where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1     Changed: 1     Warnings: 0

mysql> select * from testdate;
+----+------------+----------+---------------------+
| id | time          | current     | combine                |
+----+------------+----------+---------------------+
| 1     | 2007-05-04 | 21:18:00 | 2005-05-04 21:22:00 |
+----+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql> select * from testdate where month(time)=5;
+----+------------+----------+---------------------+
| id | time        | current   | combine              |
+----+------------+----------+---------------------+
| 1   | 2007-05-04 | 21:18:00 | 2005-05-04 21:22:00 |
+----+------------+----------+---------------------+
1 row in set (0.00 sec)

 

 

# java.lang.ClassCastException: java.util.Date cannot be cast to java.sql.Date  
#   
# 解决方法：  
# java.util.Date date1=new java.util.Date();  
# java.sql.Date date2=new java.sql.Date(date1.getTime());