ORA-01830: date format image ends before converting entire input string to TOAD
I have a script in which I write functions and procedures. The script works fine in Oracle SQL developer without any errors or warnings. This script I have to provide to the client on the client side. Client user is executing the same script in TOAD
and he gets an error
ORA-01830: date format picture ends before converting entire input string
In this situation, I really don't know how to handle errors like this. Is this a bug in TOAD
? I use to_date
and to_timestamp
in my functions and there I get an error.
source to share
You either rely on implicit date conversions or implicit format models; since you said you are using to_date
and to_timestamp
seems to be the latter. You didn’t provide your code, but the error means that you are calling these functions with a string value in the format you expect to see, but you don’t explicitly provide the format model as the second argument to the function. This will use the NLS_DATE_FORMAT session setting. If you do:
to_date('12/06/2015 09:10:11')
then you are really doing effectively:
to_date('12/06/2015 09:10:11', (select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'))
You have no control over how your client sets up their NLS environment, and therefore you should never rely on implicit conversions or NLS assumptions.
As the documentation says :
Note: It is
good practice to always specify the format mask (fmt) with TO_DATE, as shown in the examples in the next section. When used without a format mask, the function is only valid if the char uses the same format as defined by the NLS_TERRITORY or NLS_DATE_FORMAT parameters. In addition, the function can be unstable across databases if no explicit format mask is specified to avoid dependencies.
You can see how this affects a simple query:
SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> select to_date('12/06/2015 09:10:11') from dual;
TO_DATE('12/06/2015
-------------------
12/06/2015 09:10:11
SQL> alter session set nls_date_format = 'DD/MM/YYYY';
Session altered.
SQL> select to_date('12/06/2015 09:10:11') from dual;
select to_date('12/06/2015 09:10:11') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
For the same reason, you should avoid month names, as their interpretation depends on NLS_DATE_LANGUAGE; although you can at least override this at the query level if you really need to.
You can also consider date and time literals if you are working with fixed values:
select date '2015-06-12' ...
select timestamp '2015-06-12 09:10:11' ...
but if you are creating a string to convert to date then this is not a good fit.
source to share