Which picture does the ORA-01830 error message refer to

oracleoracle-11g-r2

ORA-01830: date format picture ends before converting entire input string

I am confused. Which picture is the error message talking about? All I provided was a date.

Best Answer

The "format picture" refers to the format mask. If you are doing an explicit conversion using the TO_DATE function, the format picture is the second argument

to_date( input_string, format_picture )

i.e.

to_date( '2011-01-01 01:23:45', 'YYYY-MM-DD' )

In this case, the format picture is the "YYYY-MM-DD". This expression raises the ORA-01830 error because the input string contains more data than the format picture is capable of handling.

If, on the other hand, you are doing an implicit conversion, the format picture refer's to the session's NLS_DATE_FORMAT which Oracle uses in its own implicit TO_DATE call.

Related Solutions

Oracle Error – Literal Does Not Match Format String

Oracle doesn't understand the literal date format strings, as they are not in the same format that your client is configured for. You need to use the TO_DATE function to tell Oracle the format of the date string you are presenting it with, like so:

AND (ACTIVITY.ACTIVITY_START_DATE >= TO_DATE('2014-02-05 00:00:00','YYYY-MM-DD HH24:MI:SS')) 
AND (ACTIVITY.ACTIVITY_START_DATE < TO_DATE('2014-02-06 00:00:00','YYYY-MM-DD HH24:MI:SS')));

You could also represent your dates using ANSI syntax (seeing as they have no time component):

AND (ACTIVITY.ACTIVITY_START_DATE >= DATE'2014-02-05') 
AND (ACTIVITY.ACTIVITY_START_DATE < DATE'2014-02-06'));

Oracle – Resolving ORA-01830 Error for 11.2.0 Client

Is possible that for the 10g client you either have a the date format set in a local login.sql file or set in the $ORACLE_HOME/sqlplus/admin/glogin.sql. If you can find this date formatting, then you should set it the same for your 11g client and it should work.

Essentially, when you put in a date string Oracle look at the nls_date_format to see how a date is formatted by default. If that is different than how you have it in a sting, Oracle might have an issue handling the date. you can also use the to_Date funtion to force a particular date format to work.

Best Answer

Related Solutions

Oracle Error – Literal Does Not Match Format String

Oracle – Resolving ORA-01830 Error for 11.2.0 Client

Related Question