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

Question

I am trying to run the query on an Oracle database

select 
  to_date(trunc(sysdate) || '13:00:00','DD-MON-YY HH24:MI:SS') 
from dual;

and get the error

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

Please suggest.

Answer 1

SQL> alter session set nls_Date_format = 'DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select trunc(sysdate, 'DD') + 13/24 from dual;

TRUNC(SYSDATE,'DD'
------------------
29-AUG-15 13:00:00

Answer 2

This is a runtime error. The date function has a problem to convert the date string. so display the date string to see what you want to convert:

select trunc(sysdate) || '13:00:00' from dual

Answer 3

to_date( trunc( sysdate ) || '13:00:00','DD-MON-YY HH24:MI:SS' )

An Innocent-looking query ... but with nasty, implicit Type Conversions all over the place.

Breaking it down:

• Start with sysdate(). (DATE Data Type)

• Truncate that value, to remove the Time portion. (Still a DATE Data Type)

• Implicitly cast that value into a VARCHAR, using whatever NLS_DATE_FORMAT happens to be in force, then concatenate the time bit on the end of that, without any intervening space to separate the two bits, and finally

• Try to convert the resulting varchar value back into a DATE Data Type, using the explicit format given (which, by the way, includes a space between the data and Time portions).

If those internal conversions don't give you something that exactly matches that Date/Time parsing pattern? Boom!

Remove the last to_date() call and see what [varchar] value you're actually getting.

I suspect this will work if you remove the explicit Date/Time "pattern" and use whatever [NLS setting] the database is currently using.