Forum Stats

  • 3,750,522 Users
  • 2,250,187 Discussions
  • 7,866,997 Comments

Discussions

ORA-01840: input value not long enough for date format

epipko
epipko Member Posts: 101 Blue Ribbon
edited Jul 22, 2021 4:04PM in SQL & PL/SQL

Hi,

Oracle 19.3 on Win2019

I am running the following query and receiving ORA-01840.

The t-year is 2022. The date format in case statement comes out as "1/1/2022".

Why am I getting this error and how do I fix it?

select  case n
      when 1 then to_date('01/01/'||''||t_year||'','mm/dd/yyyy')
      when 2 then to_date('02/01/'||''||t_year||'','mm/dd/yyyy')
      when 3 then to_date('03/01/'||''||t_year||'','mm/dd/yyyy')
      when 4 then to_date('04/01/'||''||t_year||'','mm/dd/yyyy')
      when 5 then to_date('05/01/'||''||t_year||'','mm/dd/yyyy')
      when 6 then to_date('06/01/'||''||t_year||'','mm/dd/yyyy')
      when 7 then to_date('07/01/'||''||t_year||'','mm/dd/yyyy')
      when 8 then to_date('08/01/'||''||t_year||'','mm/dd/yyyy')
      when 9 then to_date('09/01/'||''||t_year||'','mm/dd/yyyy')
      when 10 then to_date('10/01/'||''||t_year||'','mm/dd/yyyy')
      when 11 then to_date('11/01/'||''||t_year||'','mm/dd/yyyy')
      when 12 then to_date('12/01/'||''||t_year||'','mm/dd/yyyy')
    end x
from T

cross join

 select 1 n from dual
 union all
 select 2 from dual
 union all
 select 3 from dual
 union all
 select 4 from dual
 union all
 select 5 from dual
 union all
 select 6 from dual
 union all
 select 7 from dual
 union all
 select 8 from dual
 union all
 select 9 from dual
 union all
 select 10 from dual
 union all
 select 11 from dual
 union all
 select 12 from dual

 );

  

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,292 Red Diamond
    Accepted Answer

    So, once you know you've got good years coming in from your external table you can essentially use what you were doing, but use a connect by query to generate your 12 month values, and simplify the "x" date you generate... e.g.

    SQL> -- simulate the external table
    SQL> with ext_T(divi,customer,salesrep,season,t_year,t_ver,jan_sales,feb_sales,mar_sales,apr_sales,may_sales,jun_sales,jul_sales,aug_sales,sep_sales,oct_sales,nov_sales,dec_sales) as (
      2    select 'JRS', '*NEW', 'EP', '20221', 2022, 'original', 0, 10000, 12500, 2500, 10000, 5000, 0,0,0,0,0,0 from dual union all
      3    select 'JRS', '*OTHER', 'EP', '20221', 2022, 'original', 1944, 3027, 3578, 4127, 3205, 1119, 0,0,0,0,0,0 from dual
      4    )
      5  --insert into sales
      6  select
      7      divi
      8     ,customer
      9     ,salesrep
     10     ,season
     11     ,t_year
     12     ,initcap(t_ver) version
     13     ,to_date(to_char(m.m,'fm00')||t_year,'mm/yyyy') as x
     14     ,case m
     15       when 1 then jan_sales
     16       when 2 then feb_sales
     17       when 3 then mar_sales
     18       when 4 then apr_sales
     19       when 5 then may_sales
     20       when 6 then jun_sales
     21       when 7 then jul_sales
     22       when 8 then aug_sales
     23       when 9 then sep_sales
     24       when 10 then oct_sales
     25       when 11 then nov_sales
     26       when 12 then dec_sales
     27     end bookings
     28  from ext_T
     29       cross join (select level m from dual connect by level <= 12) m
     30  ;
    
    
    DIV CUSTOM SA SEASO     T_YEAR VERSION  X                      BOOKINGS
    --- ------ -- ----- ---------- -------- -------------------- ----------
    JRS *NEW   EP 20221       2022 Original 01-JAN-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-JAN-2022 00:00:00       1944
    JRS *NEW   EP 20221       2022 Original 01-FEB-2022 00:00:00      10000
    JRS *OTHER EP 20221       2022 Original 01-FEB-2022 00:00:00       3027
    JRS *NEW   EP 20221       2022 Original 01-MAR-2022 00:00:00      12500
    JRS *OTHER EP 20221       2022 Original 01-MAR-2022 00:00:00       3578
    JRS *NEW   EP 20221       2022 Original 01-APR-2022 00:00:00       2500
    JRS *OTHER EP 20221       2022 Original 01-APR-2022 00:00:00       4127
    JRS *NEW   EP 20221       2022 Original 01-MAY-2022 00:00:00      10000
    JRS *OTHER EP 20221       2022 Original 01-MAY-2022 00:00:00       3205
    JRS *NEW   EP 20221       2022 Original 01-JUN-2022 00:00:00       5000
    JRS *OTHER EP 20221       2022 Original 01-JUN-2022 00:00:00       1119
    JRS *NEW   EP 20221       2022 Original 01-JUL-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-JUL-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-AUG-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-AUG-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-SEP-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-SEP-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-OCT-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-OCT-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-NOV-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-NOV-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-DEC-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-DEC-2022 00:00:00          0
    
    
    24 rows selected.
    
    
    

    The default day for a date when created with mm/yyyy format is 1, so you don't need to specify that, and the month can be specified from the value in the cross join anyway so you don't need to manually specify that either, just use the value concatenated with the year you're getting.

    You could use the UNPIVOT functionality of SQL to unpivot the months of data, but as it's such a simple query I'm not sure it's worth it, so I'd personally be ok with sticking with the cross join and case statement.

«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,890 Red Diamond

    Hi, @epipko

    Whenever you have a question, please post a complete test script so the people who want to help you car re-create the problem and test their ideas.  I can't reproduce the problem with what you've posted; you haven't shown how you're assigning value to t_date. I suspect that t_date is not what you think it is.

    By the way, there's no point in concatenating '' to another string, so,

    '01/01/'||''||t_year||''
    

    is just a roundabpout, confusing way of saying

    '01/01/' || t_year
    

    I wouldn't use a CASE expression at all. If you want to list the DATEs on which all the months of a given year begin, you could do it this way:

    DEFINE t_year = 2022
    
    SELECT ADD_MONTHS ( TO_DATE ( '01/01/' || '&t_year'
    		   	    , 'MM/DD/YYYY'
    			    )
    		  , LEVEL - 1
    		  ) AS month_start
    FROM	dual
    CONNECT BY LEVEL <= 12;
    

    Instead of using a substitution variable (like &t_year above) you could use any other kind of variable.

    If you are using a substitution variable, then you don't need ||; you can simply say '01/01/&t_year'

  • epipko
    epipko Member Posts: 101 Blue Ribbon
    edited Jul 22, 2021 5:33PM

    Thanks Frank,

    The value of t_year is coming from external table into which I load user's spreadsheet.

    Here is the snippet of it:


    DIV	CUSTOMER	SALESREP	SEASON	T_YEAR	T_VER	   JAN_SALES	FEB_SALES	MAR_SALES	APR_SALES	MAY_SALES	JUN_SALES	JUL_SALES	AUG_SALES	SEP_SALES	OCT_SALES	NOV_SALES	DEC_SALES
    JRS	*NEW	        EP	        20221	2022	original   0	        10000	        12500	        12500	        10000	        5000						
    JRS	*OTHER	        EP	        20221	2022	original   1944	        3027	        3578	        4127	        3205	        1119						
    
    
    

    Here is how am running insert into a table:

    insert into sales 
    select --*
    	divi
    	,customer
    	,salesrep
    	,season
    	,t_year
    	,initcap(t_ver) version
    	case n
    	  when 1 then to_date('01/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 2 then to_date('02/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 3 then to_date('03/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 4 then to_date('04/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 5 then to_date('05/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 6 then to_date('06/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 7 then to_date('07/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 8 then to_date('08/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 9 then to_date('09/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 10 then to_date('10/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 11 then to_date('11/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 12 then to_date('12/01/'||''||t_year||'','mm/dd/yyyy')
    	end x
    	,case n
    	  when 1 then jan_sales
    	  when 2 then feb_sales
    	  when 3 then mar_sales
    	  when 4 then apr_sales
    	  when 5 then may_sales
    	  when 6 then jun_sales
    	  when 7 then jul_sales
    	  when 8 then aug_sales
    	  when 9 then sep_sales
    	  when 10 then oct_sales
    	  when 11 then nov_sales
    	  when 12 then dec_sales
    	end  bookings
    from ext_T
    
    cross join
                                                  (
     select 1 n from dual
     union all
     select 2 from dual
     union all
     select 3 from dual
     union all
     select 4 from dual
     union all
     select 5 from dual
     union all
     select 6 from dual
     union all
     select 7 from dual
     union all
     select 8 from dual
     union all
     select 9 from dual
     union all
     select 10 from dual
     union all
     select 11 from dual
     union all
     select 12 from dual
     ;
    
    
    
  • mathguy
    mathguy Member Posts: 9,968 Gold Crown

    You said t_year comes from "external table" (whatever that means - I suspect that you are not using the term in its technical sense).

    The simplest way that the error would be triggered is if in some cases (at least one), the value is null. If you concatenate null to something like 01/01/ the result will just be 01/01/ - which is not long enough for the date format, as the year is missing.

    Try selecting from your "external table" where t_year is null - not related to anything else, just a trivial select of a single column from a single table, with a where clause - and see what that returns. If it returns at least one row, you have your answer. If it doesn't, try also where trim(t_year) is null - just in case someone stores spaces instead of null (I have seen this nonsensical convention before, and more than once).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,890 Red Diamond

    Hi, @epipko

    The value of t_year is coming from external table into which I load user's spreadsheet.

    You still haven't shown how you assign a value to t_year. You haven't even shown what data type t_year is. I still can't reproduce the problem. I still suspect that t_year is not always what you think it is.

    It looks like you could get the results you want using SELECT ... UNPIVOT; you don't need any CASE expressions or UNIONs.

  • epipko
    epipko Member Posts: 101 Blue Ribbon
    select * from ext_T
    where t_year is null OR trim(t_year) is null;
    

    returns no rows

  • mathguy
    mathguy Member Posts: 9,968 Gold Crown

    Perfect - so you are, indeed, using the term in the technical sense. And the issue is not null input (or all-spaces).

    Are all the values valid numbers? What happens if you select to_number(t_year) from ext_t? As you can see, I am just guessing - trying to figure out, through small queries, what the problem may be. You can also try this yourself - think of what may cause the problem you saw, and run small tests to see if those various reasons may in fact explain the issue.

    As an aside, note that the code snippets you posted have syntax errors (so you are obviously not just copying and pasting). Is it possible that, perhaps, the error doesn't come from the place you suspect, but from some other place you didn't even include in your posts here?

    Examples of syntax errors: the cross join is to something that should be enclosed in parentheses; in the initial question you are missing the opening parenthesis but you have the closing one, while in your later reply both parentheses are missing. Also in the latest reply, you are missing a comma before the case expression in the select list.

  • epipko
    epipko Member Posts: 101 Blue Ribbon

    Frank,

    Here is the external table definition:

    CREATE TABLE UB_OBJECTS.GM_TEMPLATE_EXT
    (
      DIV   		VARCHAR2(5 BYTE),
      CUSTOMER   	VARCHAR2(10 BYTE),
      SALESREP   	VARCHAR2(10 BYTE),
      SEASON        VARCHAR2(6 BYTE),
      T_YEAR     	NUMBER(4),
      VERSION  		VARCHAR2(30 BYTE),
      JAN_SALES     NUMBER(10),
      FEB_SALES     NUMBER(10),
      MAR_SALES     NUMBER(10),
      APR_SALES     NUMBER(10),
      MAY_SALES     NUMBER(10),
      JUN_SALES     NUMBER(10),
      JUL_SALES     NUMBER(10),
      AUG_SALES     NUMBER(10),
      SEP_SALES     NUMBER(10),
      OCT_SALES     NUMBER(10),
      NOV_SALES     NUMBER(10),
      DEC_SALES     NUMBER(10)
    )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY GOALS
         ACCESS PARAMETERS 
           ( 
             records delimited BY newline
             skip 1
             BADFILE ext_dir
             FIELDS TERMINATED BY ',' LRTRIM
           	 MISSING FIELD VALUES ARE NULL
             (
    				  DIV
    				  ,CUSTOMER
    				  ,SALESREP
    				  ,SEASON
    				  ,T_YEAR
    				  ,VERSION
    				  ,JAN_SALES
    				  ,FEB_SALES
    				  ,MAR_SALES
    				  ,APR_SALES
    				  ,MAY_SALES
    				  ,JUN_SALES
    				  ,JUL_SALES
    				  ,AUG_SALES
    				  ,SEP_SALES
    				  ,OCT_SALES
    				  ,NOV_SALES
    				  ,DEC_SALES
    		  )
           )
         LOCATION (GOALS:'goals.csv')
      )
    REJECT LIMIT UNLIMITED
    NOPARALLEL
    /
    

    I am also attaching sample .csv file, but I had to change extension to .txt

    Here is the insert statement

    insert into sales 
    select --*
    	divi
    	,customer
    	,salesrep
    	,season
    	,t_year
    	,initcap(t_ver) version
    	case n
    	  when 1 then to_date('01/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 2 then to_date('02/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 3 then to_date('03/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 4 then to_date('04/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 5 then to_date('05/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 6 then to_date('06/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 7 then to_date('07/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 8 then to_date('08/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 9 then to_date('09/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 10 then to_date('10/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 11 then to_date('11/01/'||''||t_year||'','mm/dd/yyyy')
    	  when 12 then to_date('12/01/'||''||t_year||'','mm/dd/yyyy')
    	end x
    	,case n
    	  when 1 then jan_sales
    	  when 2 then feb_sales
    	  when 3 then mar_sales
    	  when 4 then apr_sales
    	  when 5 then may_sales
    	  when 6 then jun_sales
    	  when 7 then jul_sales
    	  when 8 then aug_sales
    	  when 9 then sep_sales
    	  when 10 then oct_sales
    	  when 11 then nov_sales
    	  when 12 then dec_sales
    	end  bookings
    from ext_T
    cross join
     (
     select 1 n from dual
     union all
     select 2 from dual
     union all
     select 3 from dual
     union all
     select 4 from dual
     union all
     select 5 from dual
     union all
     select 6 from dual
     union all
     select 7 from dual
     union all
     select 8 from dual
     union all
     select 9 from dual
     union all
     select 10 from dual
     union all
     select 11 from dual
     union all
     select 12 from dual
     )
     ;
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,890 Red Diamond

    Hi, @epipko

    Here is the external table definition:

    The table in your code is called ext_t, not gm_template_ext. Are you sure you're using the right table?

    T_YEAR     	NUMBER(4),
    

    TO_DATE and || work on strings, not NUMBERs. If this table is only being used for this INSERT, then declare t_year VARCHAR2 (4). If t_year needs to be a NUMBER in some other statement, then try using TO_CHAR (t_year, 'FY0000') in this statement. However, that's probably not the problem. I still suspect you have some invalid data. Try this query

    SELECT DISTINCT
              t_year
    ,         DUMP (t_year)  AS dmp
    FROM      gm_template_ext  -- or ext_t, or whatever the name is
    ORDER BY  t_year;
    

    to check for bad data.

  • epipko
    epipko Member Posts: 101 Blue Ribbon

    I asked for complete .csv file and found that there is, in fact an empty row at the end of file that was not deleted.

    As soon as I deleted it, the original query worked. I still want to rewrite my original query into what you, Frank proposed.

    I really appreciate both of your responses.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,292 Red Diamond
    Accepted Answer

    So, once you know you've got good years coming in from your external table you can essentially use what you were doing, but use a connect by query to generate your 12 month values, and simplify the "x" date you generate... e.g.

    SQL> -- simulate the external table
    SQL> with ext_T(divi,customer,salesrep,season,t_year,t_ver,jan_sales,feb_sales,mar_sales,apr_sales,may_sales,jun_sales,jul_sales,aug_sales,sep_sales,oct_sales,nov_sales,dec_sales) as (
      2    select 'JRS', '*NEW', 'EP', '20221', 2022, 'original', 0, 10000, 12500, 2500, 10000, 5000, 0,0,0,0,0,0 from dual union all
      3    select 'JRS', '*OTHER', 'EP', '20221', 2022, 'original', 1944, 3027, 3578, 4127, 3205, 1119, 0,0,0,0,0,0 from dual
      4    )
      5  --insert into sales
      6  select
      7      divi
      8     ,customer
      9     ,salesrep
     10     ,season
     11     ,t_year
     12     ,initcap(t_ver) version
     13     ,to_date(to_char(m.m,'fm00')||t_year,'mm/yyyy') as x
     14     ,case m
     15       when 1 then jan_sales
     16       when 2 then feb_sales
     17       when 3 then mar_sales
     18       when 4 then apr_sales
     19       when 5 then may_sales
     20       when 6 then jun_sales
     21       when 7 then jul_sales
     22       when 8 then aug_sales
     23       when 9 then sep_sales
     24       when 10 then oct_sales
     25       when 11 then nov_sales
     26       when 12 then dec_sales
     27     end bookings
     28  from ext_T
     29       cross join (select level m from dual connect by level <= 12) m
     30  ;
    
    
    DIV CUSTOM SA SEASO     T_YEAR VERSION  X                      BOOKINGS
    --- ------ -- ----- ---------- -------- -------------------- ----------
    JRS *NEW   EP 20221       2022 Original 01-JAN-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-JAN-2022 00:00:00       1944
    JRS *NEW   EP 20221       2022 Original 01-FEB-2022 00:00:00      10000
    JRS *OTHER EP 20221       2022 Original 01-FEB-2022 00:00:00       3027
    JRS *NEW   EP 20221       2022 Original 01-MAR-2022 00:00:00      12500
    JRS *OTHER EP 20221       2022 Original 01-MAR-2022 00:00:00       3578
    JRS *NEW   EP 20221       2022 Original 01-APR-2022 00:00:00       2500
    JRS *OTHER EP 20221       2022 Original 01-APR-2022 00:00:00       4127
    JRS *NEW   EP 20221       2022 Original 01-MAY-2022 00:00:00      10000
    JRS *OTHER EP 20221       2022 Original 01-MAY-2022 00:00:00       3205
    JRS *NEW   EP 20221       2022 Original 01-JUN-2022 00:00:00       5000
    JRS *OTHER EP 20221       2022 Original 01-JUN-2022 00:00:00       1119
    JRS *NEW   EP 20221       2022 Original 01-JUL-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-JUL-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-AUG-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-AUG-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-SEP-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-SEP-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-OCT-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-OCT-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-NOV-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-NOV-2022 00:00:00          0
    JRS *NEW   EP 20221       2022 Original 01-DEC-2022 00:00:00          0
    JRS *OTHER EP 20221       2022 Original 01-DEC-2022 00:00:00          0
    
    
    24 rows selected.
    
    
    

    The default day for a date when created with mm/yyyy format is 1, so you don't need to specify that, and the month can be specified from the value in the cross join anyway so you don't need to manually specify that either, just use the value concatenated with the year you're getting.

    You could use the UNPIVOT functionality of SQL to unpivot the months of data, but as it's such a simple query I'm not sure it's worth it, so I'd personally be ok with sticking with the cross join and case statement.