0

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.

Improve this question
0
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
Improve this answer
3
  • 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
0

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
Improve this answer
0

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.

Improve this answer

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