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.

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 '15 at 11:38
  • note: the alter session is only necessary to demonstrate the result. It is not part of the solution. – miracle173 Aug 29 '15 at 15:21
  • I prefer trunc(sysdate) + interval '13' hour for clarity. – a_horse_with_no_name Aug 30 '15 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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