Wednesday, September 19, 2007

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

If you are getting this error in Oracle Application Express, see this other topic otherwise, carry on.

This type of error can be easily demonstrated:
select TO_DATE('2007/09/19 10:00', 'YYYY/MM/DD')  date_fmt
from dual;

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

In this scenario it's very easy to spot the problem, because it's clearly caused by an input string longer than the date format mask (16 char string instead of the expected 10 char string), however it may be less obvious when an implicit conversion is occurring.

for instance:

CREATE TABLE TEST1 (d DATE)
/
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'
/
declare
a varchar2(20) := '19/09/2007 12:00:00';
begin
INSERT INTO TEST1 (d) VALUES (a);
end;
/
ORA-01830: date format picture ends before converting entire input string

So, when you get ORA-01830, you must always determine what the current NLS_DATE_FORMAT is and see if there is some implicit conversion going on.

See message translations for ORA-01830 and search additional resources

----------------------------
ORA-01830: Il formato data termina prima di convertire l'intera stringa in input
ORA-01830: la máscara de formato de fecha termina antes de convertir toda la cadena de entrada
ORA-01830: el format de la data no coincideix amb la cadena a convertir que s'ha entrat
ORA-01830: données surnuméraires après la conversion correcte d'une chaîne en entrée
ORA-01830: Datumsformatstruktur endet vor Umwandlung der gesamten Eingabezeichenfolge
ORA-01830: πρότυπο μορφής ημερομηνίας τελειώνει πριν να γίνει μετατροπή όλου του αλφαριθμητικού εισόδου
ORA-01830: datoformatbillede slutter før konvertering af hele inputstrengen
ORA-01830: datumformatmasken slutar före hela inmatningssträngen omvandlats
ORA-01830: datoformatbildet slutter før hele inndatastrengen er konvertert
ORA-01830: päivämäärämuodon kuvain päättyy ennen koko syötemerkkijonon muunnosta
ORA-01830: a dátumformátum véget ért a teljes bemeneti karakterlánc konverziója előtt
ORA-01830: imaginea formatului de dată se termină înaintea conversiei şirului de intrare
ORA-01830: Datumnotatieafbeelding eindigt voordat de gehele invoerstring is geconverteerd.
ORA-01830: a imagem do formato da data termina antes de converter a string de entrada inteira
ORA-01830: imagem de formato de data termina antes de converter toda a cadeia de caracteres
ORA-01830: шаблон формата даты завершается перед преобразованием всей строки ввода
ORA-01830: obraz formátu pro daum končí před převodem celého vstupního řetězce
ORA-01830: zadaný formát dátumu nepostačuje pre úplné konvertovanie vstupného reťazca
ORA-01830: wzorzec formatu daty kończy się przed konwersją całego ciągu wejściowego
ORA-01830: tarih formatı resmi tüm girdi dizesi dönüştürülmeden önce son buldu
ORA-01830: date format picture ends before converting entire input string

2 comments:

madhu said...

hi

Please try the below one

SELECT to_char(TO_DATE('08/12/2007 11:00 ','MM/DD/YYYY HH:MI'),'MM-DD-YYYY') FROM DUAL;

i hope this is correct.

welcome........................

Byte64 said...

Hi Madhu,
it works as expected.
What's wrong with it?
Is perhaps the trailing blank the reason of your concern?
Blanks don't affect the result, they will be trimmed wherever they are found in the input string.

Bye,
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles