CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Rupert Cobbe-Warburton
Rupert Cobbe-Warburton

Reputation: 663

Oracle - Comparing dates

I have this Oracle procedure:

CREATE OR REPLACE PROCEDURE xmas
IS
    CURSOR curUser IS SELECT userID, coins FROM Users;
    thisUser curUser%ROWTYPE;
    d VARCHAR(7);
    CURSOR curDate IS SELECT TO_CHAR(sysdate, 'DD-MON') FROM DUAL;
BEGIN
    OPEN curDate;
    FETCH curDate INTO d;
    CLOSE curDate;
    IF ((TO_DATE(d)) = (TO_DATE('25-DEC'))) THEN
        OPEN curUser;
        LOOP
            FETCH curUser INTO thisUser;
            EXIT WHEN (curUser%NOTFOUND);
            thisUser.coins := thisUser.coins + 5.00;
            UPDATE Users SET coins = thisUser.coins WHERE userID = thisUser.userID;
        END LOOP;
        CLOSE curUser;
    END IF;
END xmas;

and when I call it, I get this error:

ORA-01840: input value not long enough for date format.

Tried different comparison methods for hours and nothing else has worked. What's the problem??

Upvotes: 0

Views: 1199

Answers (1)

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

You need to specify a date format for Oracle to know what actually '25-DEC' means.

select TO_DATE('25-DEC', 'DD-MON') from dual;

The problem should be on this line:

    IF ((TO_DATE(d)) = (TO_DATE('25-DEC'))) THEN

As a note, it seems to me that you are also making a lot of unnecessary conversions. I don't know if this is an educational attempt, but why not just run the update below:

update USERS 
set
  coins = coins + 5
where
  to_char(sysdate, 'DD-MON') = '25-DEC';

Upvotes: 2

Related Questions