If you pass TO_DAYS( ) a date-and-time value, it extracts the date part and discards the time.

     
mysql>
mysql>
mysql>
mysql> CREATE TABLE datetime_val
    -> (
    ->  dt      DATETIME
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO datetime_val (dt) VALUES('1970-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO datetime_val (dt) VALUES('1987-03-05 12:30:15');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO datetime_val (dt) VALUES('1999-12-31 09:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO datetime_val (dt) VALUES('2000-06-04 15:45:30');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM datetime_val;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
+---------------------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT dt,
    -> TO_DAYS(dt) AS 'date part in days',
    -> FROM_DAYS(TO_DAYS(dt)) AS 'date part as DATE'
    -> FROM datetime_val;
+---------------------+-------------------+-------------------+
| dt                  | date part in days | date part as DATE |
+---------------------+-------------------+-------------------+
| 1970-01-01 00:00:00 |            719528 | 1970-01-01        |
| 1987-03-05 12:30:15 |            725800 | 1987-03-05        |
| 1999-12-31 09:00:00 |            730484 | 1999-12-31        |
| 2000-06-04 15:45:30 |            730640 | 2000-06-04        |
+---------------------+-------------------+-------------------+
4 rows in set (0.00 sec)

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

mysql>
mysql>
mysql> CREATE TABLE timestamp_val
    -> (
    ->  ts      TIMESTAMP
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO timestamp_val (ts) VALUES('1970-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp_val (ts) VALUES('1987-03-05 12:30:15');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp_val (ts) VALUES('1999-12-31 09:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamp_val (ts) VALUES('2000-06-04 15:45:30');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM timestamp_val;
+---------------------+
| ts                  |
+---------------------+
| 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
+---------------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT ts,
    -> TO_DAYS(ts) AS 'date part in days',
    -> FROM_DAYS(TO_DAYS(ts)) AS 'date part as DATE'
    -> FROM timestamp_val;
+---------------------+-------------------+-------------------+
| ts                  | date part in days | date part as DATE |
+---------------------+-------------------+-------------------+
| 1970-01-01 00:00:00 |            719528 | 1970-01-01        |
| 1987-03-05 12:30:15 |            725800 | 1987-03-05        |
| 1999-12-31 09:00:00 |            730484 | 1999-12-31        |
| 2000-06-04 15:45:30 |            730640 | 2000-06-04        |
+---------------------+-------------------+-------------------+
4 rows in set (0.00 sec)

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

Related examples in the same category

1.	TO_DAYS( ) can convert DATETIME or TIMESTAMP values to days, if you don't mind having it chop off the time par
2.	Using two date-and-time values that lie a week apart
3.	TO_DAYS( ) converts a date to the corresponding number of days
4.	Calculating Intervals Between Dates
5.	For an interval in weeks, do the same thing and divide the result by seven

If you pass TO_DAYS( ) a date-and-time value, it extracts the date part and discards the time. : TO_DAYS « Date Time « SQL / MySQL

Related examples in the same category