Convert Date to int : Date Type « Date Time « SQL / MySQL

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

Convert Date to int

  
/*
mysql> Drop table Transactions;
Query OK, 0 rows affected (0.09 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);
Query OK, 23 rows affected (0.06 sec)
Records: 23  Duplicates: 0  Warnings: 0

mysql> select * from Transactions;
+----+---------+-------+------------+------------+------------+
| ID | OrderID | DVDID | DateOut    | DateDue    | DateIn     |
+----+---------+-------+------------+------------+------------+
|  1 |       1 |     1 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
|  2 |       1 |     4 | 2005-10-08 | 2005-10-09 | 0000-00-00 |
|  3 |       1 |     8 | 2005-10-08 | 2005-10-10 | 0000-00-00 |
|  4 |       2 |     3 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
|  5 |       3 |     4 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
|  6 |       3 |     1 | 2005-10-08 | 2005-10-13 | 0000-00-00 |
|  7 |       3 |     7 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
|  8 |       4 |     4 | 2005-10-08 | 2005-10-15 | 0000-00-00 |
|  9 |       5 |     3 | 2005-10-08 | 2005-10-16 | 0000-00-00 |
| 10 |       6 |     2 | 2005-10-08 | 2005-10-15 | 0000-00-00 |
| 11 |       6 |     1 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
| 12 |       7 |     4 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
| 13 |       8 |     2 | 2005-10-08 | 2005-10-13 | 0000-00-00 |
| 14 |       8 |     1 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
| 15 |       8 |     3 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
| 16 |       9 |     7 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
| 17 |       9 |     1 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
| 18 |      10 |     5 | 2005-10-08 | 2005-10-09 | 0000-00-00 |
| 19 |      11 |     6 | 2005-10-08 | 2005-10-10 | 0000-00-00 |
| 20 |      11 |     2 | 2005-10-08 | 2005-10-14 | 0000-00-00 |
| 21 |      11 |     8 | 2005-10-08 | 2005-10-13 | 0000-00-00 |
| 22 |      12 |     5 | 2005-10-08 | 2005-10-12 | 0000-00-00 |
| 23 |      13 |     7 | 2005-10-08 | 2005-10-11 | 0000-00-00 |
+----+---------+-------+------------+------------+------------+
23 rows in set (0.01 sec)

mysql> SELECT OrderID, ID, DVDID,
    ->    CONVERT(DateOut, UNSIGNED) AS DateOut_INT
    -> FROM Transactions
    -> WHERE DVDID=4 OR DVDID=5 OR DVDID=7
    -> ORDER BY OrderID, ID, DVDID;
+---------+----+-------+-------------+
| OrderID | ID | DVDID | DateOut_INT |
+---------+----+-------+-------------+
|       1 |  2 |     4 |    20051008 |
|       3 |  5 |     4 |    20051008 |
|       3 |  7 |     7 |    20051008 |
|       4 |  8 |     4 |    20051008 |
|       7 | 12 |     4 |    20051008 |
|       9 | 16 |     7 |    20051008 |
|      10 | 18 |     5 |    20051008 |
|      12 | 22 |     5 |    20051008 |
|      13 | 23 |     7 |    20051008 |
+---------+----+-------+-------------+
9 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 * from Transactions;

SELECT OrderID, ID, DVDID,
   CONVERT(DateOut, UNSIGNED) AS DateOut_INT
FROM Transactions
WHERE DVDID=4 OR DVDID=5 OR DVDID=7
ORDER BY OrderID, ID, DVDID;

           
         
    
  

Related examples in the same category