MySQL FROM_DAYS() and TO_DAYS()

By /
From Days To Days

In this tutorial, we will learn about the MySQL FROM_DAYS() and TO_DAYS() functions.

  • The MySQL FROM_DAYS() function returns the date value from a given numeric date value. The specified numeric date value is divided by 365. The resulting remainder is added to the year 0 and accordingly, the date value is returned. I know it is a bit confusing but the examples below will hopefully clear it up for you. 
  • The MySQL TO_DAYS() function is the exact opposite of the FROM_DAYS() function. It returns the number of days between a date and the year 0 (date “0000-00-00”). 

Note that both functions can be used only with dates within the Gregorian calendar. Let us know take a look at the syntaxes and examples of these two functions.

Syntax of MySQL FROM_DAYS()

FROM_DAYS(number)Code language: SQL (Structured Query Language) (sql)

Where ‘number’ is the numeric date value that is to be converted to a date value.

Syntax of MySQL TO_DAYS()

TO_DAYS(date)Code language: SQL (Structured Query Language) (sql)

Where ‘date’ is a date value which TO_DAYS() will use to find the number of days between ‘date’ and year 0.

Examples of MySQL FROM_DAYS()

Let’s take a look at the examples of the MySQL FROM_DAYS() function here.

The Working of MySQL FROM_DAYS()

Let us take the opportunity of using the below basic examples to understand the working of the FROM_DAYS() function. The year 0 is considered the start point. As you know, every year (apart from a leap year) has 365 days. So let us pass 1 and 365 to find the first and last dates from the year 0. Let us use the SELECT statement for this.

SELECT FROM_DAYS(1); 
SELECT FROM_DAYS(365);Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL FROM_DAYS()

Wait, why did it return ‘0000-00-00’ in both cases? That is because MySQL recognizes the first 365 days as a part of year 0 and only starts counting from day 366. And that is why the specified numeric date value is divided by 365. The resulting remainder is added to the year 0 and accordingly, the date value is returned.

In the case of 366, the resulting remainder is 1, so that gets added to year 0 and the first date after year 0 is 0001-01-01. Similarly, if we pass 367, the remainder is 2 and the date returned is 0001-01-02. We can see this using the below queries – 

SELECT FROM_DAYS(366); 
SELECT FROM_DAYS(367);Code language: SQL (Structured Query Language) (sql)

And the output is –

From Days 2

Passing Mathematical Functions to MySQL FROM_DAYS()

We can pass mathematical functions like POW() to the MySQL FROM_DAYS() function. How about finding the 1000th date and the 1000000th date since the year 0. We will use the POW() function for this.

SELECT FROM_DAYS(POW(10, 3)); SELECT FROM_DAYS(POW(100, 3));Code language: SQL (Structured Query Language) (sql)

And the output is –

From Days 3

Examples of MySQL TO_DAYS()

Now let us see some basic examples of the TO_DAYS() function. TO_DAYS() is the exact opposite of the FROM_DAYS() function. So if we pass the date ‘0001-01-01’ to the TO_DAYS() function, what might be the output? Let’s see that using the below example.

SELECT TO_DAYS('0001-01-01');Code language: SQL (Structured Query Language) (sql)

And the output is –

To Days 1

Similarly, we can pass different dates to the TO_DAYS() function. Let’s take a look at the below examples.

SELECT TO_DAYS('1999-12-31'); 
SELECT TO_DAYS('2021-03-05');Code language: SQL (Structured Query Language) (sql)

And the output is –

To Days 2

MySQL TO_DAYS() With A Date From Year 0

TO_DAYS() returns NULL if we pass it any date value of the year 0. This happens because MySQL starts counting the date values only after the year 0 even though it may count the number of days in the year 0. Let us see an example of this.

SELECT TO_DAYS('0000-00-00');Code language: SQL (Structured Query Language) (sql)

And the output is –

To Days 3

MySQL TO_DAYS() With CURDATE()

Yes, if you want to find out the number of days elapsed since the beginning of time (literally), you can pass the CURDATE() function to the TO_DAYS() function. Let us demonstrate this using the below query. We will use an alias to make our output readable.

SELECT CURDATE() AS Today, TO_DAYS(CURDATE());Code language: SQL (Structured Query Language) (sql)

And the output is –

To Days 5

Working With Tables

Consider the below Employee table.

Employee Table
Employee Table

Let us compare the FROM_DAYS() and TO_DAYS() functions and display the Date_Joined column, the number of days between every employee and the year 0 and then convert that value back to the date value using the FROM_DAYS() Function. The query is –

SELECT Date_Joined, 
TO_DAYS(Date_Joined), 
FROM_DAYS(TO_DAYS(Date_Joined)) FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

From Days To Days Table Example

Conclusion

In this tutorial, we learned about MySQL FROM_DAYS() and TO_DAYS() functions. There is a lot more we can do with these functions and I encourage you to explore that and practice these queries.

References