CODEWITHSUNDEEP
sqloracle-database
kyooryu
kyooryu

Reputation: 1509

Oracle - literal does not match format string

I have a function which takes 2 date parameters:

CREATE OR REPLACE FUNCTION date_equal
(
    date1 IN DATE,
    date2 IN DATE
)
RETURN NUMBER IS
    equal BOOLEAN;
BEGIN
    equal := NVL(date1, '1999-01-01') = NVL(date2, '1999-01-01');
    IF equal THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END date_equal;
/

Now, when I run select on the table which provides data for the function it runs okay:

SELECT TO_DATE(some_date, 'YYYY-MM-DD') FROM tbl

But when I try to use that in function call it fails:

SELECT date_equal(TO_DATE(some_date, 'YYYY-MM-DD'), TO_DATE(some_date, 'YYYY-MM-DD')) FROM tbl

The error message is "literal does not match format string". Does anyone know why would that happen?

Upvotes: 0

Views: 1334

Answers (2)

René Nyffenegger
René Nyffenegger

Reputation: 40499

The error message is most certainly caused by the pieace reading NVL(date1, '1999-01-01').

Try nvl(date1, date '1999-01-01') instead.

Upvotes: 2

A.B.Cade
A.B.Cade

Reputation: 16905

When you do 

NVL(date1, '1999-01-01')

Oracle tries to convert '1999-01-01' to a date implicitly (since date1 is a date).
For doing this it uses NLS_DATE_FORMAT which may not be yyyy-mm-dd

You can use explicit converting:

NVL(date1, to_date('1999-01-01', 'yyyy-mm-dd'))

or use the ANSI way

NVL(date1, date '1999-01-01')

Upvotes: 4

Related Questions