CODEWITHSUNDEEP
oracle-databaseplsql
user14259526
user14259526

Reputation:

Why I get error Invalid Number and what does it mean?

I am trying to understand what is wrong in my query, but whatever I try I get same error.

 SELECT uta.StartDate, uta.EndDate FROM user_timesheets_absence uta
  WHERE uta.UserID = 381
  AND uta.Approved = 0
  AND 2020-01-20 BETWEEN uta.StartDate AND uta.EndDate + INTERVAL 1 DAY

StartDate and EndDate are store as DATE and I pass format which is correctly. I try to change format but nothing happened, same error. I also try to remove time but also nothink happened.

Here is table definition:

ABSENCETYPE VARCHAR2(255 CHAR)
ANSWER  VARCHAR2(500 CHAR)
APPROVED    NUMBER(10,0)
COMMENT_    CLOB
DAYS    NUMBER(10,0)
ENDDATE DATE
ISANSWERED  NUMBER(10,0)
ISREJECTED  NUMBER(10,0)
STARTDATE   DATE
USERABSENCEID   NUMBER(10,0)
USERHASSEEN VARCHAR2(500 CHAR)
USERID  NUMBER(10,0)
WIDTH   NUMBER(10,0)

Some output which I expect needs to look like following

StartDate       EndDate
2020-01-01      2020-01-28

Upvotes: 0

Views: 85

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

You've modified query you initially wrote; now it really is invalid.

Should be - according to info you posted:

SELECT uta.StartDate, uta.EndDate
  FROM user_timesheets_absence uta
 WHERE     uta.UserID = 381
       AND uta.Approved = 0
       AND DATE '2020-01-20' BETWEEN uta.StartDate
                                 AND uta.EndDate + INTERVAL '1' DAY
  • userid is NUMBER so yes - you can use 381
  • approved is NUMBER so yes - you can use 0
  • startdate and enddate are DATEs so yes - you can add interval '1' day

There's nothing else that could be wrong here (or, at least, I don't see it).

Upvotes: 1

Related Questions