How to convert julian date to normal date in MySQL

In this article, we will learn how to convert a Julian date to the calendar date with MySQL.

First, let’s see how it looks like a date in Julian format.

Julian calendar and Julian date format

The Julian calendar was used since the time of Julio Cesar, specifically 45 years before Christ. It was used until 1582 when Pope Gregory XIII introduced the Gregorian calendar that we currently use.

The dates in the Julian calendar can have several formats but usually, the first digits indicate the year and the last ones indicate the days that have elapsed in that year. In this article, we will use the following format: YYYYDDD.

Where YYYY indicates the year, and DDD indicates the number of days elapsed in that year.

Let’s see some examples:

Table 1. Julian date conversion examples

MySQL julian date conversion

To illustrate our example, we will use an Orders table with the following structure and data:

CREATE TABLE `orders_table` (   `id` int(11),   `client_id` int(11),   `date_received` integer,   `date_processed` integer,   `total_amount` float,   `total_paid` float ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (1, 1, 2010079, 2010082, 100, 75); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (2, 1, 2011110, 2011112, 50.2, 20.5); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (3, 2, 2012125, 2012126, 75, 60); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (4, 2, 2013200, 2013200, 90.7, 20); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (5, 3, 2014225, 2014228, 120, 100); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (6, 1, 2015052, 2015054, 500, NULL); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (7, 1, 2016123, 2016124, 150, 150); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (8, 3, 2017312, 2017312, 200, 50); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (9, 2, 2018224, 2018227, 250, 250); INSERT INTO `orders_table` (`id`, `client_id`, `date_received`, `date_processed`, `total_amount`, `total_paid`) VALUES (10, 3, 2019360, 2019363, 300, 0); ALTER TABLE `orders_table` ADD PRIMARY KEY (`id`);

Script 1. Orders table creation

We can execute the following command to verify the contents of the Orders table:

SELECT * FROM `orders_table`

Script 2. Query to retrieve Orders table content

Result:

Table 2. Orders table content

As we can see, the received and processed fields have saved the dates in Julian YYYYDDD format.

Let’s build a query to convert the dates of the received field from Julian to normal Calendar:

SELECT `id`, `client_id`, `date_received` as `Received Julian`, MAKEDATE(LEFT(`date_received`,4) , `date_received` % 1000) as `Received Normal Calendar`, `total_amount`, `total_paid` FROM `orders_table`

Script 3. Query to convert julian date to calendar date

To build our query, we rely on the LEFT function to obtain the four digits of the year and the % operator to calculate the elapsed days.

Result:

Table 3. Received field with a new format