SQL/MySQL/Date Time/Date Time Function

Using Date/Time Functions in Your SQL Statements

   <source lang="sql">

/* 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;

      </source>