MySQL - FROM_DAYS() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL FROM_DAYS() function accepts a numerical value representing the number of days since year 0 to the particular date as a parameter and returns the date value.

Syntax

Following is the syntax of the above function –

FROM_DAYS(N)

Example 1

Following example demonstrates the usage of the FROM_DAYS() function –

mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
|     0001-01-01 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(400);
+----------------+
| FROM_DAYS(400) |
+----------------+
|     0001-02-04 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(100992);
+-------------------+
| FROM_DAYS(100992) |
+-------------------+
|        0276-07-04 |
+-------------------+
1 row in set (0.00 sec)

Example 2

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

mysql> SELECT FROM_DAYS(21);
+---------------+
| FROM_DAYS(21) |
+---------------+
|    0000-00-00 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(100);
+----------------+
| FROM_DAYS(100) |
+----------------+
|     0000-00-00 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(290);
+----------------+
| FROM_DAYS(290) |
+----------------+
|     0000-00-00 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS(365);
+----------------+
| FROM_DAYS(365) |
+----------------+
|     0000-00-00 |
+----------------+
1 row in set (0.00 sec)

Example 3

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

mysql> SELECT FROM_DAYS('3599999');
+----------------------+
| FROM_DAYS('3599999') |
+----------------------+
|           9856-06-18 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS('3,600,000');
+------------------------+
| FROM_DAYS('3,600,000') |
+------------------------+
|             0000-00-00 |
+------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT FROM_DAYS('3666585');
+----------------------+
| FROM_DAYS('3666585') |
+----------------------+
|           0000-00-00 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_DAYS('39999985');
+-----------------------+
| FROM_DAYS('39999985') |
+-----------------------+
|            0000-00-00 |
+-----------------------+
1 row in set (0.00 sec)

Example 4

You can also pass expression as an argument to this function.

mysql> SELECT FROM_DAYS(365.25*2021);
+------------------------+
| FROM_DAYS(365.25*2021) |
+------------------------+
|             2021-01-15 |
+------------------------+
1 row in set (0.00 sec)

Example 5

You can also pass the column name as an argument to this function. Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth_Days INT,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', 723884, 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', 723657, 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', 722384, 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', 726411, 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', 725856, 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', 726442, 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', 724091, 'Burnley', 'England');

Following query converts the Date_Of_Birth_Days values of all the players into date and prints them –

mysql> SELECT First_Name, Last_Name, Date_Of_Birth_Days, Country, FROM_DAYS(Date_Of_Birth_Days) as DateOfBirth FROM MyPlayers;
+------------+------------+--------------------+-------------+-------------+
| First_Name | Last_Name  | Date_Of_Birth_Days | Country     | DateOfBirth |
+------------+------------+--------------------+-------------+-------------+
| Shikhar    | Dhawan     | 723884             | India       | 1981-12-05  |
| Jonathan   | Trott      | 723657             | SouthAfrica | 1981-04-22  |
| Kumara     | Sangakkara | 722384             | Srilanka    | 1977-10-27  |
| Virat      | Kohli      | 726411             | India       | 1988-11-05  |
| Rohit      | Sharma     | 725856             | India       | 1987-04-30  |
| Ravindra   | Jadeja     | 726442             | India       | 1988-12-06  |
| James      | Anderson   | 724091             | England     | 1982-06-30  |
+------------+------------+--------------------+-------------+-------------+
7 rows in set (0.00 sec)

Example 6

Let us create another table with name Sales in MySQL database using CREATE statement as follows –

mysql> CREATE TABLE sales(
	ID INT,
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchDateDays INT,
	Price INT,
	Location VARCHAR(255)
);
Query OK, 0 rows affected (2.22 sec)

Now, we will insert 5 records in Sales table using INSERT statements −

insert into sales values (1, 'Key-Board', 'Raja', 737668, 7000, 'Hyderabad');
insert into sales values (2, 'Earphones', 'Roja', 737545, 2000, 'Vishakhapatnam');
insert into sales values (3, 'Mouse', 'Puja', 737484, 3000, 'Vijayawada');
insert into sales values (4, 'Mobile', 'Vanaja', 737484, 9000, 'Chennai');
insert into sales values (5, 'Headset', 'Jalaja', 737520, 6000, 'Goa');

In the following query we are passing the column (name) DispatchDate as an argument of this function –

mysql> SELECT ProductName, CustomerName, DispatchDateDays, Price, FROM_DAYS(DispatchDateDays) as Days FROM sales;
+-------------+--------------+------------------+-------+------------+
| ProductName | CustomerName | DispatchDateDays | Price |       Days |
+-------------+--------------+------------------+-------+------------+
| Key-Board   | Raja         | 737668           | 7000  | 2019-09-01 |
| Earphones   | Roja         | 737545           | 2000  | 2019-05-01 |
| Mouse       | Puja         | 737484           | 3000  | 2019-03-01 |
| Mobile      | Vanaja       | 737484           | 9000  | 2019-03-01 |
| Headset     | Jalaja       | 737520           | 6000  | 2019-04-06 |
+-------------+--------------+------------------+-------+------------+
5 rows in set (0.00 sec)

Example 7

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDateDays INT
);
insert into SubscriberDetails values('Raja', 'Premium', 738084);
insert into SubscriberDetails values('Roja', 'Basic', 738120);
insert into SubscriberDetails values('Puja', 'Moderate', 738221);
insert into SubscriberDetails values('Vanaja', 'Basic', 738207);
insert into SubscriberDetails values('Jalaja', 'Premium', 738185);

Following query retrieves the subscription date value from the SubscriptionDateDays column.

mysql> SELECT SubscriberName, PackageName, SubscriptionDateDays, FROM_DAYS(SubscriptionDateDays) AS SubscriptionDate FROM SubscriberDetails;
+----------------+-------------+----------------------+------------------+
| SubscriberName | PackageName | SubscriptionDateDays | SubscriptionDate |
+----------------+-------------+----------------------+------------------+
| Raja           | Premium     | 738084               | 2020-10-21       |
| Roja           | Basic       | 738120               | 2020-11-26       |
| Puja           | Moderate    | 738221               | 2021-03-07       |
| Vanaja         | Basic       | 738207               | 2021-02-21       |
| Jalaja         | Premium     | 738185               | 2021-01-30       |
+----------------+-------------+----------------------+------------------+
5 rows in set (0.00 sec)