Use BETWEEN and AND for a date data type : Date Compare Order « Date Time « SQL / MySQL

1. SQL / MySQL
2. Date Time
3. Date Compare Order

Use BETWEEN and AND for a date data type

/*
mysql> Drop table timeTable;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE timeTable (
    ->     year YEAR(4),
    ->     month INT(2) UNSIGNED ZEROFILL,
    ->     day INT(2) UNSIGNED ZEROFILL
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO timeTable VALUES(2001,1,1),
    ->                             (2002,6,20),
    ->                             (2003,5,30),
    ->                             (2004,2,2),
    ->                             (2005,4,23),
    ->                             (2006,3,23);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from timeTable;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2001 |    01 |   01 |
| 2002 |    06 |   20 |
| 2003 |    05 |   30 |
| 2004 |    02 |   02 |
| 2005 |    04 |   23 |
| 2006 |    03 |   23 |
+------+-------+------+
6 rows in set (0.01 sec)

mysql> select * from TimeTable WHERE year BETWEEN 2001 AND 2003;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2001 |    01 |   01 |
| 2002 |    06 |   20 |
| 2003 |    05 |   30 |
+------+-------+------+
3 rows in set (0.00 sec)


*/

Drop table timeTable;

CREATE TABLE timeTable (
    year YEAR(4), 
    month INT(2) UNSIGNED ZEROFILL,
    day INT(2) UNSIGNED ZEROFILL
);

INSERT INTO timeTable VALUES(2001,1,1),
                            (2002,6,20),
                            (2003,5,30),
                            (2004,2,2),
                            (2005,4,23),
                            (2006,3,23);

select * from timeTable;
  
select * from TimeTable WHERE year BETWEEN 2001 AND 2003; 
           
       

Related examples in the same category