CODEWITHSUNDEEP
sqloracle-database
Jeff
Jeff

Reputation: 8148

PL/SQL Food Industry "Julian" Date Conversion

I have some 5 digits dates in a format that the food industry generally calls "Julian" dates (Warning: these are not the "Julian" dates you are familiar with. It's misused terminology that "stuck" and became the standard in the industry. Don't comment on that, it's just how it is.)

The first 3 digits of these "Julian" dates are a number representing the day of a year. 

Example: 
    January 1 = 001
    January 2 = 002
    December 31 = 365 or 366

The next two digits are the last two digits of the year. 2015 = 15.

For example 22215 = August 10, 2015 (I believe).

I need an Oracle SQL statement that convers these "dates" into standard dates to join to other date data.

Upvotes: 1

Views: 198

Answers (2)

Jeff
Jeff

Reputation: 8148

A co-worker found the answer. The "DDD" format:

SELECT to_date('36515', 'DDDYY') FROM DUAL

Upvotes: 1

are
are

Reputation: 2615

I'd add (count of days) -1 to 1st Jan of year

select to_date('20'||15||'01.01','yyyy.mm.dd') + 222-1 from dual

10/08/2015

Upvotes: 1

Related Questions