Date calculation with variable : Date « Data Type « SQL / MySQL






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

Date calculation with variable

    
mysql>
mysql>
mysql> CREATE   TABLE PENALTIES
    ->         (PAYMENTNO      INTEGER      NOT NULL,
    ->          EmployeeNO       INTEGER      NOT NULL,
    ->          PAYMENT_DATE   DATE         NOT NULL,
    ->          AMOUNT         DECIMAL(7,2) NOT NULL,
    ->          PRIMARY KEY    (PAYMENTNO)          );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO PENALTIES VALUES (1,  6, '1980-12-08',100);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (6,  8, '1980-12-08', 25);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SET @TODAY = CURRENT_DATE;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT @TODAY := CURRENT_DATE;
+------------------------+
| @TODAY := CURRENT_DATE |
+------------------------+
| 2011-10-05             |
+------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT   *
    -> FROM     PENALTIES
    -> WHERE    PAYMENT_DATE < @TODAY - INTERVAL 5 YEAR;
+-----------+------------+--------------+--------+
| PAYMENTNO | EmployeeNO | PAYMENT_DATE | AMOUNT |
+-----------+------------+--------------+--------+
|         1 |          6 | 1980-12-08   | 100.00 |
|         2 |         44 | 1981-05-05   |  75.00 |
|         3 |         27 | 1983-09-10   | 100.00 |
|         4 |        104 | 1984-12-08   |  50.00 |
|         5 |         44 | 1980-12-08   |  25.00 |
|         6 |          8 | 1980-12-08   |  25.00 |
|         7 |         44 | 1982-12-30   |  30.00 |
|         8 |         27 | 1984-11-12   |  75.00 |
+-----------+------------+--------------+--------+
8 rows in set (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 type value inside in operator and subquery
8.Date default value
9.Sorting by Calendar Day