Sql error ora 01830 шаблон формата даты завершается перед преобразованием всей строки ввода

Hi ,
I am runing the following statment and get result back as follow:

SELECT d.table_name, d.partition_name, d.tablespace_name,
  2                 to_date(SUBSTR (d.partition_name,
  3                         2,
  4                         LENGTH (d.partition_name) - 5
  5                         )
  6                 || SUBSTR (d.partition_name,
  7                     LENGTH (d.partition_name) - 3,
  8                     LENGTH (d.partition_name)
  9                     ) ,'YYYYMMDD') part_date
 10                 FROM user_tab_partitions d
 11                 WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                PART_DATE
------------------------------ ------------------------------ ------------------------------ ---------
RADIUS_LOG                     P20091111                      RADIUS_DATA_TS                 11-NOV-09
RADIUS_LOG                     P20091112                      RADIUS_DATA_TS                 12-NOV-09
RADIUS_LOG                     P20091113                      RADIUS_DATA_TS                 13-NOV-09

After adding the part_date to the where clause i am getting : ORA-01830: date format picture .
Why ?

 select t.table_name,t.partition_name, t.tablespace_name,to_date(t.part_date,'dd/mm/rrrr') p_date
  2    from(
  3    SELECT d.table_name, d.partition_name, d.tablespace_name,
  4                  to_date(SUBSTR (d.partition_name,
  5                          2,
  6                          LENGTH (d.partition_name) - 5
  7                          )
  8                  || SUBSTR (d.partition_name,
  9                      LENGTH (d.partition_name) - 3,
 10                      LENGTH (d.partition_name)
 11                      ) ,'YYYYMMDD') part_date
 12                  FROM user_tab_partitions d
 13                  WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
 14         ) t 
 15     where to_date(t.part_date,'dd/mm/rrrr') < to_date(sysdate,'dd/mm/rrrr') - 7;
                || SUBSTR (d.partition_name,
                 *
ERROR at line 8:
ORA-01830: date format picture ends before converting entire input strin

Thanks

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

ORA-01830 date format picture ends before converting entire input string
Cause: A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required.
Action: Check the specifications for date format pictures and correct the statement.

ORA-01830 is a common oracle error, it usually occurs when date value is entered, date format does not match with the date value.

We can reproduce ORA-01830 in many ways, followings are very common:

SQL> DESC EMP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 HIREDATE                                           DATE
 
SQL> insert into emp values (101,'Nimish','16-May-2007 09:54');
insert into emp values (101,'Nimish','16-May-2007 09:54')
                                     *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

or we may simply reproduce it by simply using to_date

SQL> select to_date('16-May-2007 09:54') from dual;
select to_date('16-May-2007 09:54') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


SQL> select to_date('16-May-2007 09:54','dd-mon-yyyy') from dual;
select to_date('16-May-2007 09:54','dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Solution:
To resolve ORA-01830, we need to simply use following tips as rules when working with conversion of string to date.
— TO_DATE must be used with FORMAT while converting string to date
— The FORMAT MUST match the string data.

So to resolve ORA-01830 issues we reproduced here we can simply modify our queries to

SQL> insert into emp values (101,'Nimish',to_date('16-May-2007 09:54','dd-Mon-yyyy hh24:mi'));
1 row created.

SQL> select to_date('16-May-2007 09:54','dd-Mon-yyyy hh24:mi') from dual;
TO_DATE('
---------
16-MAY-07

SQL> select to_date('16-May-2007 09:54','dd-Mon-yyyy hh24:mi:ss') from dual;
TO_DATE('
---------
16-MAY-07

One thing here to note is that in my last example data was ’16-May-2007 09:54′ and format was ‘dd-Mon-yyyy hh24:mi:ss’ and it worked even when there was no value for «:ss». So ORA-01830 is raised only when string value contains extra than the format.

Related Posts:
— Why to Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
— Playing With Truncate and Date
— Oracle: Some Important Date Queries
— Date Difference in Days, Months and Years
— Dates Difference in days, hours, minutes & seconds
— ORA-01403: no data found