CODEWITHSUNDEEP
sqloracle-databasedatejulian-date
user2488578
user2488578

Reputation: 916

ORA-01848: day of year must be between 1 and 365 (366 for leap year) error

I have existing data with day of the year (1-366) stored in DDD format.

Now, when I am trying to query the data and pull out report in MM/DD/YYYY format, I am getting ORA-01848: day of year must be between 1 and 365 (366 for leap year) for the below query

select to_CHAR(TO_DATE(MyColumn, 'DDD'),'MM/DD/YYYY') from MyTable;

How to retrieve date in MM/DD/YYYY format when the year is leap year?

Upvotes: 0

Views: 4329

Answers (2)

Alex Poole
Alex Poole

Reputation: 191415

Your current query:

select to_CHAR(TO_DATE(MyColumn, 'DDD'),'MM/DD/YYYY') from MyTable;

is defaulting to the current year. All of the converted values will show 2018:

-- CTE for dummy values (<= 365)
with mytable(mycolumn) as (
  select 1 from dual
  union all select 60 from dual
  union all select 365 from dual
)
select to_CHAR(TO_DATE(MyColumn, 'DDD'),'MM/DD/YYYY') from MyTable;

TO_CHAR(TO
----------
01/01/2018
03/01/2018
12/31/2018

As 2018 isn't a leap year, day 366 isn't valid. You could make it use an arbitrary hard-coded leap year:

select to_CHAR(TO_DATE('2000' || MyColumn, 'YYYYDDD'),'MM/DD/YYYY') from MyTable;

Demo:

-- CTE for dummy values
with mytable(mycolumn) as (
  select 1 from dual
  union all select 60 from dual
  union all select 365 from dual
  union all select 366 from dual
)
select to_CHAR(TO_DATE('2000' || MyColumn, 'YYYYDDD'),'MM/DD/YYYY') from MyTable;

TO_CHAR(TO
----------
01/01/2000
02/29/2000
12/30/2000
12/31/2000

But if the original date wasn't from a leap year then the values will be out by a day - as well as being shown against the wrong year, of course.

You could filter out values with values > 365 and stick to the current year, but again you're likely to get unrealistic/unhelpful converted dates. Or you could use 12c's default ... on conversion error syntax to get say a null result when it won't convert, but again other dates will be inconsistent.

Unless you know the year each DDD values represents you can't get an accurate conversion.

If you have another column that holds the year then concatenate them together, e.g. if that year column is called MyYear:

select to_CHAR(TO_DATE(MyYear || MyColumn, 'YYYYDDD'),'MM/DD/YYYY') from MyTable;

Demo showing varying resulsts:

-- CTE for dummy values
with mytable(mycolumn, myyear) as (
  select 1, 2018 from dual
  union all select 60, 2016 from dual
  union all select 60, 2017 from dual
  union all select 365, 2016 from dual
  union all select 366, 2016 from dual
  union all select 365, 2017 from dual
)
select MyColumn, MyYear,
  to_CHAR(TO_DATE(MyColumn default null on conversion error, 'DDD'),'MM/DD/YYYY') as Y2018,
  to_CHAR(TO_DATE('2000' || MyColumn, 'YYYYDDD'),'MM/DD/YYYY') as Y2000,
  to_CHAR(TO_DATE(MyYear || MyColumn, 'YYYYDDD'),'MM/DD/YYYY') as OK
from MyTable;

  MYCOLUMN     MYYEAR Y2018      Y2000      OK        
---------- ---------- ---------- ---------- ----------
         1       2018 01/01/2018 01/01/2000 01/01/2018
        60       2016 03/01/2018 02/29/2000 02/29/2016
        60       2017 03/01/2018 02/29/2000 03/01/2017
       365       2016 12/31/2018 12/30/2000 12/30/2016
       366       2016            12/31/2000 12/31/2016
       365       2017 12/31/2018 12/30/2000 12/31/2017

Upvotes: 4

XING
XING

Reputation: 9886

I created a logic which would calculate the date as per the input day. When its not a leap year, it would display the date. Incase of leap year it would display the last day if day number is 366. If it's not a leap year, it would display the last day of the year if day number is 366, hence it would never result in the error you faced.

with tb(col1) as (Select level 
                  from dual
                  connect by level < 367)
-- Actual Query ..Col1 is the day number being passed.                                                    
Select  Case when col1 < 366 then
        to_char(TO_DATE(col1, 'DDD'),'MM/DD/YYYY') 
        else
         to_char(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Year'),11)),'MM/DD/YYYY')
        end col
from tb ;

DEMO

Upvotes: 1

Related Questions