MySQL TO_DAYS() Function

The MySQL TO_DAYS() function converts a date into numeric days. This function returns the number of days between a given date/datetime value and date "0000-00-00". It returns NULL if datetime is '0000-00-00'.

This function is to be used only with dates within the Gregorian calendar.

Note: The FROM_DAYS() function which is the reverse of the TO_DAYS() function.

Syntax

TO_DAYS(datetime)

Parameters

datetime Required. Specify a date or datetime value to convert to numeric days.

Return Value

Returns the converted numeric days.

Example 1:

The example below shows the usage of TO_DAYS() function.

mysql> SELECT TO_DAYS('2018-08-18');
Result: 737289

mysql> SELECT TO_DAYS('2018-08-18 10:38:42');
Result: 737289

mysql> SELECT TO_DAYS('2018-08-18 10:38:42.000004');
Result: 737289

mysql> SELECT TO_DAYS('2018-08-19');
Result: 737290

mysql> SELECT TO_DAYS('0000-01-01');
Result: 1

mysql> SELECT TO_DAYS('0000-00-00');
Result: NULL

Example 2:

Consider a database table called Employee with the following records:

EmpID	Name	City	Age	Date_of_Joining
1	John	London	25	2018-05-25
2	Marry	New York	24	2018-10-15
3	Jo	Paris	27	2019-06-09
4	Kim	Amsterdam	30	2019-09-21
5	Ramesh	New Delhi	28	2019-10-25
6	Suresh	Mumbai	28	2021-12-26

The statement given below can be used to convert the records of column Date_of_Joining into numeric days:

SELECT *, TO_DAYS(Date_of_Joining) AS TO_DAYS_Value FROM Employee;

This will produce the result as shown below:

EmpID	Name	City	Age	Date_of_Joining	TO_DAYS_Value
1	John	London	25	2018-05-25	737204
2	Marry	New York	24	2018-10-15	737347
3	Jo	Paris	27	2019-06-09	737584
4	Kim	Amsterdam	30	2019-09-21	737688
5	Ramesh	New Delhi	28	2019-10-25	737722
6	Suresh	Mumbai	28	2021-12-26	738515

❮ MySQL Functions