Date type value inside in operator and subquery : Date « Data Type « SQL / MySQL






  1. SQL / MySQL
  2. Data Type
  3. Date

Date type value inside in operator and subquery

    
mysql>
mysql>
mysql>
mysql> CREATE   TABLE COMMITTEE_MEMBERS
    ->         (EmployeeNO       INTEGER      NOT NULL,
    ->          BEGIN_DATE     DATE         NOT NULL,
    ->          END_DATE       DATE                 ,
    ->          POSITION       CHAR(20)             ,
    ->          PRIMARY KEY    (EmployeeNO, BEGIN_DATE));
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1990-01-01', '1990-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1991-01-01', '1992-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1992-01-01', '1993-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  6, '1993-01-01',  NULL, 'Chairman');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  2, '1990-01-01', '1992-12-31', 'Chairman');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  2, '1994-01-01',  NULL, 'Member');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-01-01', '1992-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-01-01',  NULL, 'Secretary');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1990-01-01', '1990-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1991-01-01', '1991-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1993-01-01', '1993-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES (  8, '1994-01-01',  NULL, 'Member');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-01-01', '1992-12-31', 'Secretary');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-01-01', '1990-12-31', 'Member');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-01-01', '1991-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-01-01', '1993-12-31', 'Treasurer');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-01-01',  NULL, 'Treasurer');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT   *
    -> FROM     COMMITTEE_MEMBERS
    -> WHERE   (BEGIN_DATE, END_DATE) IN
    ->         (SELECT   BEGIN_DATE, END_DATE
    ->          FROM     COMMITTEE_MEMBERS
    ->          WHERE    POSITION = 'Secretary');
+------------+------------+------------+-----------+
| EmployeeNO | BEGIN_DATE | END_DATE   | POSITION  |
+------------+------------+------------+-----------+
|          6 | 1990-01-01 | 1990-12-31 | Secretary |
|        112 | 1992-01-01 | 1992-12-31 | Member    |
|          8 | 1990-01-01 | 1990-12-31 | Treasurer |
|          8 | 1991-01-01 | 1991-12-31 | Secretary |
|         57 | 1992-01-01 | 1992-12-31 | Secretary |
|         27 | 1990-01-01 | 1990-12-31 | Member    |
|         27 | 1991-01-01 | 1991-12-31 | Treasurer |
+------------+------------+------------+-----------+
7 rows in set (0.00 sec)

mysql>
mysql> drop table COMMITTEE_MEMBERS;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Date type value in the form '2003-12-31', range 1000-01-01 to 9999-12-31 (3 bytes)
2.To find the row with the most recent birth date, the query is similar, except that you sort in descending orde
3.Compare with Date value
4.How MySQL deals with incorrect date value
5.Date literal in where clause
6.Date value literal
7.Date calculation with variable
8.Date default value
9.Sorting by Calendar Day