MySQL - FROM_DAYS() Function

The MySQL FROM_DAYS() function accepts a numerical value representing the number of days counted from the 0th year based on the Gregorian Calendar and returns the date value.

Using this function, the specified date value is divided by 365 and accordingly years, months, and days are returned. For instance, if we pass 370 as an argument to the function, the value is divided by 365 and 5 is obtained as the remainder. Since, the date value is retrieved according to the Gregorian Calendar, the date returned in this case would be 0001-01-05.

Note: The count starts from the first year according to modern calendars, and hence, if the argument passed to this function is less than 366, the return value will always be '0000-00-00'.

Syntax

Following is the syntax of MySQL FROM_DAYS() function −

FROM_DAYS(N);

Parameters

This method accepts a number representing the number of days since the start of the MySQL date system as a parameter.

Return value

This function converts a number of days since the start of the MySQL date system into a date. It returns a date value.

Example

In the following query, we are using the MySQL FROM_DAYS() function to convert 400 into a date format and returns the corresponding date −

SELECT FROM_DAYS(400) As Result;

Output

Following output is obtained −

Result
0001-02-04

Example

The argument value passed to this function is less than 366 (first year according to modern calendars) this function returns 0000-00-00.

SELECT FROM_DAYS(21) As Result;

Output

Following output is obtained −

Result
0000-00-00

Example

If the value of the day is more than 3599999, this function returns 0000-00-00.

SELECT FROM_DAYS('3599999') As Result;

This will produce the following result −

Result
9856-06-18

Here, we passing a value more than 3599999 to this function −

SELECT FROM_DAYS('3666585') As Result;

Following output is obtained −

Result
0000-00-00

Example

We can also pass expression as an argument to this function as shown below −

SELECT FROM_DAYS(365.25*2021) As Result;

Output

This will produce the following result −

Result
2021-01-15

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL FROM_DAYS() function to calculate the result by converting the values in the "AMOUNT" column into a date format −

SELECT OID, DATE, FROM_DAYS(AMOUNT) as Result FROM Orders;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 0008-03-19
100 2009-10-08 00:00:00 0008-03-19
101 2009-11-20 00:00:00 0004-04-09
103 2008-05-20 00:00:00 0005-08-22
Advertisements