Stored Procedure Error with function Interval on Timestamp field

Database

Stored Procedure Error with function Interval on Timestamp field

Hi everyone!

I'm a newbie on Teradata (TD).

I want to create a stored procedure (SP) with a parameter (parameter_init_date) to fill incrementally a table named t_new.

I use the field dt_mvt in t_new to check the last date of data loaded .

 

First run, my table t_new is empty so I use the parameter parameter_init_date to load data from starting date (parameter_init_date) to the ending date (parameter_init_date + 1 month) .

SET v_date_deb=CAST(parameter_init_date AS TIMESTAMP);
SET v_date_fin=v_date_deb + (INTERVAL '1' MONTH);

 

2nd run, my table t_new is not empty. Then starting date is red from table t_new as max date :

SELECT MAX(dt_mvt) INTO :v_date_maj FROM t_new;

This statement retrieves me a timestamp value.

 

 

On the first run, my SP works fine!! my table t_new contains one month data as expected.

On the 2nd run SP retrieves error:  Executed as Single statement. Failed [2665 : 22007] t_new:Invalid date. Elapsed time = 00:00:00.100 

 

I've tried to debug and error occurs when I used interval function on timestamp (dt_mvt is a timestamp field)

IF (v_date_maj IS NULL) THEN
	SET v_date_deb=CAST(parameter_init_date AS TIMESTAMP);
ELSE 
	SET v_date_deb=v_date_maj;
END IF;
SET v_date_fin=v_date_deb + INTERVAL '1' MONTH;

 

here is the full code i use to debug values:

REPLACE PROCEDURE TOTO.MY_FIRST_SP(ARG_DATE_DEBUT DATE)
-- call TOTO.MY_FIRST_SP('2018-12-31');

BEGIN
	-- Déclaration des variables
	DECLARE v_date_maj TIMESTAMP;
	DECLARE v_date_deb TIMESTAMP;
	DECLARE v_date_fin TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
   
   
	-- Check date values
	SELECT MAX(dt_mvt) INTO :v_date_maj FROM toto.t_new;
	
	IF (v_date_maj IS NULL) THEN
		SET v_date_deb=CAST(ARG_DATE_DEBUT AS TIMESTAMP);
	ELSE 
		SET v_date_deb=v_date_maj;
	END IF;
	
	SET v_date_fin=v_date_deb + INTERVAL '1' MONTH;
	
	INSERT INTO fbo_test (param1, param2, param3)
	VALUES (v_date_maj, v_date_deb, v_date_fin); 
		
END;

Any help will be kindfull.

 

 

Thanks a lot !

 

FM.

 


Accepted Solutions
Ambassador

Re: Stored Procedure Error with function Interval on Timestamp field

Never use INTERVAL MONTH/YEAR, it will always fail for non-existing date, e.g. '2019-01-31' + interval '1' month results in '2019-02-31' (blame Standard SQL).

Use add_months (returns '2019-05-30' for '2019-04-30', i.e. the same day when it exists, otherwise the previous day) or  oAdd_months (returns '2019-05-31' for '2019-04-30', i.e. always the last day of a month when you start on the last day of a month):

 

	SET v_date_fin= oADD_MONTHS(v_date_deb, 1)
1 ACCEPTED SOLUTION
3 REPLIES 3
Ambassador

Re: Stored Procedure Error with function Interval on Timestamp field

Never use INTERVAL MONTH/YEAR, it will always fail for non-existing date, e.g. '2019-01-31' + interval '1' month results in '2019-02-31' (blame Standard SQL).

Use add_months (returns '2019-05-30' for '2019-04-30', i.e. the same day when it exists, otherwise the previous day) or  oAdd_months (returns '2019-05-31' for '2019-04-30', i.e. always the last day of a month when you start on the last day of a month):

 

	SET v_date_fin= oADD_MONTHS(v_date_deb, 1)
Highlighted

Re: Stored Procedure Error with function Interval on Timestamp field

Thanks Dieter!

It runs well with function add_months  

Nevertheless, when I used the second function oAdd_months I can't compile my SP. I get this error message :

Executed as Single statement.  Failed [5526 : HY000] Stored Procedure is not created/replaced due to error(s).{Nested Failure Msg [5526 : HY000] SPL1027:E(L36), Missing/Invalid SQL statement'E(5407):Invalid operation for DateTime or Interval.'.}
Elapsed time = 00:00:00.350
STATEMENT 1: REPLACE  failed. .

If you have more information, it will be helpfull.

 

Thanks a lot!

Ambassador

Re: Stored Procedure Error with function Interval on Timestamp field

Oops, for a timestamp oAdd_months returns a DATE while add_months returns a TIMESTAMP.

You might try an explicit CAST

SET v_date_fin=Cast(OAdd_Months(v_date_deb, 1) AS TIMESTAMP);

but then you'll loose the time portion and get midnight returned.

 

But it looks lke you're not using the time portion at all, then you might switch to DATEs instead of TIMESTAMPs