Using Date/Time Functions in Your SQL Statements : Date Time Function « Date Time « SQL / MySQL

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

Using Date/Time Functions in Your SQL Statements

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

mysql> CREATE TABLE Transactions (
    ->    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    OrderID INT NOT NULL,
    ->    DVDID SMALLINT NOT NULL,
    ->    DateOut DATE NOT NULL,
    ->    DateDue DATE NOT NULL,
    ->    DateIn DATE NOT NULL
    ->    )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
    -> VALUES (1, 1, CURDATE(), CURDATE()+3),
    ->        (1, 4, CURDATE(), CURDATE()+1),
    ->        (1, 8, CURDATE(), CURDATE()+2),
    ->        (2, 3, CURDATE(), CURDATE()+3),
    ->        (3, 4, CURDATE(), CURDATE()+4),
    ->        (3, 1, CURDATE(), CURDATE()+5),
    ->        (3, 7, CURDATE(), CURDATE()+6),
    ->        (4, 4, CURDATE(), CURDATE()+7),
    ->        (5, 3, CURDATE(), CURDATE()+8),
    ->        (6, 2, CURDATE(), CURDATE()+7),
    ->        (6, 1, CURDATE(), CURDATE()+6),
    ->        (7, 4, CURDATE(), CURDATE()+6),
    ->        (8, 2, CURDATE(), CURDATE()+5),
    ->        (8, 1, CURDATE(), CURDATE()+4),
    ->        (8, 3, CURDATE(), CURDATE()+4),
    ->        (9, 7, CURDATE(), CURDATE()+3),
    ->        (9, 1, CURDATE(), CURDATE()+3),
    ->        (10, 5, CURDATE(), CURDATE()+1),
    ->        (11, 6, CURDATE(), CURDATE()+2),
    ->        (11, 2, CURDATE(), CURDATE()+6),
    ->        (11, 8, CURDATE(), CURDATE()+5),
    ->        (12, 5, CURDATE(), CURDATE()+4),
    ->        (13, 7, CURDATE(), CURDATE()+3);SELECT ID, YEAR(DateOut) AS YearOu
t
Query OK, 23 rows affected (0.08 sec)
Records: 23  Duplicates: 0  Warnings: 0

    -> FROM Transactions
    -> WHERE ID>15
    -> ORDER BY ID;
+----+---------+
| ID | YearOut |
+----+---------+
| 16 |    2005 |
| 17 |    2005 |
| 18 |    2005 |
| 19 |    2005 |
| 20 |    2005 |
| 21 |    2005 |
| 22 |    2005 |
| 23 |    2005 |
+----+---------+
8 rows in set (0.00 sec)

*/

Drop table Transactions;

CREATE TABLE Transactions (
   ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   OrderID INT NOT NULL,
   DVDID SMALLINT NOT NULL,
   DateOut DATE NOT NULL,
   DateDue DATE NOT NULL,
   DateIn DATE NOT NULL
   )
ENGINE=INNODB;

INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)
VALUES (1, 1, CURDATE(), CURDATE()+3),
       (1, 4, CURDATE(), CURDATE()+1),
       (1, 8, CURDATE(), CURDATE()+2),
       (2, 3, CURDATE(), CURDATE()+3),
       (3, 4, CURDATE(), CURDATE()+4),
       (3, 1, CURDATE(), CURDATE()+5),
       (3, 7, CURDATE(), CURDATE()+6),
       (4, 4, CURDATE(), CURDATE()+7),
       (5, 3, CURDATE(), CURDATE()+8),
       (6, 2, CURDATE(), CURDATE()+7),
       (6, 1, CURDATE(), CURDATE()+6),
       (7, 4, CURDATE(), CURDATE()+6),
       (8, 2, CURDATE(), CURDATE()+5),
       (8, 1, CURDATE(), CURDATE()+4),
       (8, 3, CURDATE(), CURDATE()+4),
       (9, 7, CURDATE(), CURDATE()+3),
       (9, 1, CURDATE(), CURDATE()+3),
       (10, 5, CURDATE(), CURDATE()+1),
       (11, 6, CURDATE(), CURDATE()+2),
       (11, 2, CURDATE(), CURDATE()+6),
       (11, 8, CURDATE(), CURDATE()+5),
       (12, 5, CURDATE(), CURDATE()+4),
       (13, 7, CURDATE(), CURDATE()+3);



SELECT ID, YEAR(DateOut) AS YearOut
FROM Transactions
WHERE ID>15
ORDER BY ID;



           
       

Related examples in the same category