Input year in the specified range

Question

I have a query and apple CASE funcation and getting error

(full) year must be between -4713 and +9999, and not be 0

This code is yielding the error:

NVL(     
CASE WHEN Ea.ASGACTSTART < add_months(SYSDATE,-12)  THEN 365
ELSE 
to_number( to_char(to_date('1','J') +
((SYSDATE -1) - Ea.ASGACTSTART), 'J') )
END , 0)  "Adujsted Days",

What is the purpose of `to_number( to_char(to_date('1','J')`? — Wernfried Domscheit, Jul 18 '17 at 13:44

score 0 · Answer 1 · answered Jul 18 '17 at 13:52

0

I think you can simply make

CASE WHEN Ea.ASGACTSTART < add_months(SYSDATE,-12)  THEN 365
ELSE NVL(SYSDATE - 1 - Ea.ASGACTSTART, 0)
END AS "Adujsted Days"

answered Jul 18 '17 at 13:52

Wernfried Domscheit

54,457
9
76
110

What is a "single value"? Surround it by `TRUNC()` or `ROUND()` if needed. – Wernfried Domscheit Jul 18 '17 at 14:32
Thanks i have formula in Excel but i dont need any formula in Excel is there anything that i convert excel formula to in Oracel Query here is the Excel formula =+IF(ISBLANK(@Enddate),IF($StartDate – Assad Amjad Jul 18 '17 at 14:43

score 0 · Answer 2 · answered Jul 18 '17 at 14:01

Given:

create table tabby (ASGACTSTART date);
insert into tabby values (sysdate);

The problem is this part of your expression:

select 
   to_date('1','J') + ((SYSDATE -1) - Ea.ASGACTSTART)
from tabby ea;

If you change the + to , then you can see why it's a problem.

select 
   --to_date('1','J') + ((SYSDATE -1) - Ea.ASGACTSTART)
     to_date('1','J') , ((SYSDATE -1) - Ea.ASGACTSTART)
from tabby ea;

According to at least one other post, to_date('1','J') is intended to return the smallest possible date possible. However, the right-hand side of the + might be negative and therefore cause the RDBMS to complain.

The use case I created was intended to do just that.

inconsistent datatypes: expected DATE got DATE JULIAN – Assad Amjad Jul 18 '17 at 14:28 — Assad Amjad, Jul 18 '17 at 14:28

Input year in the specified range

2 Answers2