Add Month and day to a date : INTERVAL « Date Time « SQL / MySQL






  1. SQL / MySQL
  2. Date Time
  3. INTERVAL

Add Month and day to a date

    
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>
mysql> SELECT   EmployeeNO, BEGIN_DATE,
    ->          BEGIN_DATE + INTERVAL 2 MONTH + INTERVAL 3 DAY
    -> FROM     COMMITTEE_MEMBERS;
+------------+------------+------------------------------------------------+
| EmployeeNO | BEGIN_DATE | BEGIN_DATE + INTERVAL 2 MONTH + INTERVAL 3 DAY |
+------------+------------+------------------------------------------------+
|          2 | 1990-01-01 | 1990-03-04                                     |
|          2 | 1994-01-01 | 1994-03-04                                     |
|          6 | 1990-01-01 | 1990-03-04                                     |
|          6 | 1991-01-01 | 1991-03-04                                     |
|          6 | 1992-01-01 | 1992-03-04                                     |
|          6 | 1993-01-01 | 1993-03-04                                     |
|          8 | 1990-01-01 | 1990-03-04                                     |
|          8 | 1991-01-01 | 1991-03-04                                     |
|          8 | 1993-01-01 | 1993-03-04                                     |
|          8 | 1994-01-01 | 1994-03-04                                     |
|         27 | 1990-01-01 | 1990-03-04                                     |
|         27 | 1991-01-01 | 1991-03-04                                     |
|         27 | 1993-01-01 | 1993-03-04                                     |
|         57 | 1992-01-01 | 1992-03-04                                     |
|         95 | 1994-01-01 | 1994-03-04                                     |
|        112 | 1992-01-01 | 1992-03-04                                     |
|        112 | 1994-01-01 | 1994-03-04                                     |
+------------+------------+------------------------------------------------+
17 rows in set (0.00 sec)

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

mysql>








Related examples in the same category

1.The INTERVAL() function compares the first integer listed as an argument to the integers that follow the first
2.The INTERVAL clause requires an , which must be a time value in an acceptable format, and a value.
3.Calculating Intervals Between Times
4.Adding a Temporal Interval to a Time
5.Literal for timestamp type column
6.Some interval specifiers comprise both date and time parts.
7.Use the + and - operators to perform date interval addition and subtraction
8.Add date with interval
9.Get the date seven days after the payment date.
10.Add 7 day interval to a date
11.Add hour, seconds and microseconds to Date
12.Add one day to the date literal '2004-13-12'; next show the error messages.
13.To convert the interval from seconds to other units, perform the appropriate division