Search code examples
oraclesqlplusdate-formatora-01861

Simple Oracle query: literal does not match format string

I want to execute a simple function in Oracle. The signature is defined as follows:

CREATE OR REPLACE FUNCTION NewCaseListForValidation
(
                             p_fromDate in DATE,
                             p_toDate in DATE,
                             p_rowCount in INT
)
RETURN
                             SYS_REFCURSOR
IS
                             return_value SYS_REFCURSOR;
...

I should be able to execute it with:

var rc refcursor
exec :rc := newcaselistforvalidation('2010-01-01','2011-01-01',100);
print :rc

But when typing "newcaselistforvalidation('2010-01-01','2011-01-01',100)", I get:

ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at line 1

I googled a bit and it seems I can't figure out to type the date in a correct format. Can anyone help me?

Solution

  • Query NLS_PARAMETERS in Oracle- you will then be able to see what format your DB is accepting dates in.

    Typically however i use the to_date() function:

    to_date('01-01-2011','DD-MM-YYYY');

    In the UK to input my dates.