SQL Tutorial SQL Advanced SQL Database SQL References

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:

EmpIDNameCityAgeDate of Joining
1JohnLondon252018-05-25
2MarryNew York242018-10-15
3JoParis272019-06-09
4KimAmsterdam302019-09-21
5RameshNew Delhi282019-10-25
6SureshMumbai282021-12-26

The query 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:

EmpIDNameCityAgeDate of JoiningTO_DAYS_Value
1JohnLondon252018-05-25737204
2MarryNew York242018-10-15737347
3JoParis272019-06-09737584
4KimAmsterdam302019-09-21737688
5RameshNew Delhi282019-10-25737722
6SureshMumbai282021-12-26738515

❮ MySQL Functions

5