Subtracting from a JDE Julian Date

Question

I searched for a way to subtract two different JDE Julian dates and didn't find any straightforward answers.

I'm working in Amazon Redshift and needed to subtract 5 years from the current date and return it in JDE Julian format. Below is what I came up with. Basically it converts the current date to JDE Julian and then subtracts 5000 from it; which essentially subtracts 5 from the year portion (1st three digits).

Please share any updates or comments on the solution. Hopefully someone finds this thread helpful.

---

select dateadd(y, -5, current_date) as fiveYearsAgoCalendarDate,
       (date_part('y', current_date) - 1900 || 
       datediff(d, to_date(date_part('y', current_date) ||
       '-01-01', 'yyyy-mm-dd'), current_date) + 1) - 5000 as fiveYearsAgoJulianDate;

Answer 1

This will work for you

select to_date((to_number(current_date)-5000), 'YYYDDD')
from dual;