I am trying to run the query on an Oracle database

  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.


3 Answers 3

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

Session altered.

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

29-AUG-15 13:00:00
  • Shorter is select trunc(sysdate)+13/24 from dual; The trunc function 'cuts off' the time part.
    – Marco
    Aug 29, 2015 at 11:38
  • note: the alter session is only necessary to demonstrate the result. It is not part of the solution.
    – miracle173
    Aug 29, 2015 at 15:21
  • I prefer trunc(sysdate) + interval '13' hour for clarity. Aug 30, 2015 at 7:10

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

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.