-1

Below statement throws an error on 11-Jan-2019 we check the value of paydate as 20190111 and it is correct from source. but still, the code throws an error.

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

 INSERT   INTO <New_TABLE_NAME>
        SELECT   *
        FROM     <old_table_name>
        WHERE    paydate =TO_DATE(e_paydate,'YYYYMMDD');
Pratik Mokal
  • 1
  • 1
  • 3
  • 1
    If what you said is true, then Oracle has a bug. Otherwise, it is right and you're misinterpreting reality. Please, post tables' descriptions and sample data along with SQL*Plus session which proves what you're saying. – Littlefoot Mar 21 '19 at 12:18
  • Created same table with same description loaded same data for 20190111 and it is loaded perfectly. dont know on that day(11-Jan) run was failed. we have 3 run fail for same kind of date in past. (20160101, 20111011 and 20190111) if we see mm and dd values only contain 0 and 1 digit, other run are successfully completed. – Pratik Mokal Mar 21 '19 at 12:23
  • You're comparing a column with itself here `WHERE paydate =TO_DATE(paydate,'YYYYMMDD')`. Are you sure your code is like that? – vercelli Mar 21 '19 at 12:24
  • its a variable in to_date function. – Pratik Mokal Mar 21 '19 at 12:33
  • If the variable and column names are the same, how is SQL to know which to use? Maybe it defaults to the column? I am surprised it allows this, but your variables and columns must have different names, or you must qualify the column name. – jmarkmurphy Mar 21 '19 at 12:41
  • it is a v_paydate – Pratik Mokal Mar 21 '19 at 12:42
  • 1
    Please [edit your question](https://stackoverflow.com/review/suggested-edits/22530372) with the relevant details. If it is `v_paydate` and not `paydate` then edit that so the query makes sense and also include the data type of `paydate` and `v_paydate`. – MT0 Mar 21 '19 at 12:45
  • Based on that code it could be any number of things. First use a column list as suggested by @Deepshikha and rule that out. If you still have a problem, edit your question and explain what you're really doing - post the actual code you are using, and also post table definitions – Nick.Mc Aug 12 '19 at 10:22
  • What data type is the variable `e_paydate`? As others have commented, please **[edit]** your question and add the complete code of the stored procedure –  Aug 12 '19 at 10:31

2 Answers2

0
TO_DATE( date_string, format_model )

Takes a string as its first argument. Assuming paydate is a DATE data type then you are asking Oracle to implicitly convert paydate from a DATE to a string (which will use the NLS_DATE_FORMAT session parameter) and then to pass it into the TO_DATE function to convert it back to a date.

So, your query is effectively:

SELECT   *
FROM     <old_table_name>
WHERE    paydate =TO_DATE(
                    TO_CHAR(
                      paydate,
                      ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
                    ),
                    'YYYYMMDD'
                  );

If the NLS_DATE_FORMAT does not match YYYYMMDD then you will either get an exception or unexpected behaviour.

Instead, if you want to compare a date to itself and find out if its time component is at midnight then just use TRUNC:

SELECT   *
FROM     <old_table_name>
WHERE    paydate = TRUNC( paydate );
MT0
  • 143,790
  • 11
  • 59
  • 117
0

If you are using Insert command without "values", then you need to put the parameters exactly in the same order as that of the table structure.

Since it is looking for a date column where you are passing a string or something else, this error might come.

Check the column sequence of your insert command.

Deepshikha
  • 1