Oracle Error ORA-01830 – Fixing Date Format Picture Ends Before Converting Entire Input String

oracle

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.

Best Answer

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

Related Solutions

Which picture does the ORA-01830 error message refer to

The "format picture" refers to the format mask. If you are doing an explicit conversion using the TO_DATE function, the format picture is the second argument

to_date( input_string, format_picture )

i.e.

to_date( '2011-01-01 01:23:45', 'YYYY-MM-DD' )

In this case, the format picture is the "YYYY-MM-DD". This expression raises the ORA-01830 error because the input string contains more data than the format picture is capable of handling.

If, on the other hand, you are doing an implicit conversion, the format picture refer's to the session's NLS_DATE_FORMAT which Oracle uses in its own implicit TO_DATE call.

Loading XML documents to Oracle 11g DB with control file

I think the way you'll have to do it is a Jokke suggest: instead of defining the DT datatype as TIMESTAMP WITH TIME ZONE, rather define it as VARCHAR2(29) where I think that 29 characters should be enough but you could define it wider.

Then when you extract data from the XML you'll have to convert it to TIMESTAMP WITH TIME ZONE.

However, you could also define the column as an "xsd:dateTime". See http://www.adp-gmbh.ch/xml/schema.html and http://www.w3.org/TR/xmlschema-2/#dateTime

Best Answer

Related Solutions

Which picture does the ORA-01830 error message refer to

Loading XML documents to Oracle 11g DB with control file

Related Question