SQL: Convert between Julian date (YYYYDDD or YYDDD) and normal date in Oracle and PostgreSQL

Oracle

1. Convert Julian date to normal date

SELECT to_date(2016135, 'YYYYDDD') as theDate FROM dual;

THEDATE
---------
14-MAY-16

SELECT to_date(16135, 'YYDDD') as theDate FROM dual;

THEDATE
---------
14-MAY-16

2. Convert normal date to Julian date

SELECT to_number(to_char(sysdate,'YYYYDDD')) as theDate FROM dual;

 THEDATE
----------
   2016147

SELECT to_number(to_char(sysdate,'YYDDD')) as theDate FROM dual;

THEDATE
----------
     16147

PostgreSQL

1. Convert Julian date to normal date

SELECT to_date('2016135', 'YYYYDDD') AS theDate;
or
SELECT to_date(to_char(2016135, '9999999'), 'YYYYDDD') AS theDate;

  thedate
------------
 2016-05-14

SELECT to_date('16135', 'YYDDD') as theDate;
or
SELECT to_date(to_char(16135, '99999'), 'YYDDD') as theDate;

  thedate
------------
 2016-05-14

2. Convert normal date to Julian date

 SELECT to_number(to_char(current_date, 'YYYYDDD'), '9999999') as theDate;

 thedate
---------
 2016147

 SELECT to_number(to_char(current_date, 'YYDDD'), '99999') as theDate;

 thedate
---------
   16147

---------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.