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

Question

I have a script in which I am writing functions and procedures. The script works absolutely fine in Oracle SQL developer without any errors or warnings. This script I have to provide to a client side customer. The customer user runs the same script in TOAD and he gets the error message

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

In this situation, I really don't know how to handle such errors. Is this a bug in TOAD? I use to_date and to_timestamp in my functions and there I get an error.

Solution

You're either relying on implicit date conversions or implicit format models; since you said you're using to_date and to_timestamp it seems to be the latter. You haven't shown your code but the error implies you're calling those functions with a string value in the format you expect to see, but not explicitly providing the format model as the second argument to the function. That will cause the session's NLS_DATE_FORMAT setting to be used. If you do:

to_date('12/06/2015 09:10:11')

then you are really effectively doing:

to_date('12/06/2015 09:10:11', (select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'))

You cannot control how your customer will have their NLS environment configured, and so you should never rely on implicit conversions or NLS assumptions.

As it says in the documentation:

Caution:
It is good practice always to specify a format mask (fmt) with TO_DATE, as shown in the examples in the section that follows. When it is used without a format mask, the function is valid only if char uses the same format as is determined by the NLS_TERRITORY or NLS_DATE_FORMAT parameters. Furthermore, the function may not be stable across databases unless the explicit format mask is specified to avoid dependencies.

You can see how this affects a simple query with this:

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 the NLS_DATE_LANGUAGE; though you can at least override that at query level if you really have to.

You might also want to consider date and timestamp literals if you're working with fixed values:

select date '2015-06-12' ...
select timestamp '2015-06-12 09:10:11' ...

but if you're constructing the string to convert to a date then that won't be appropriate.