Archives of the TeradataForum

Message Posted: Thu, 25 Dec 2003 @ 15:44:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi, guys,

I come across an error when I call one of my stored procedure. Teradata versiion is V2R5.0 for Windows, but the SP is called correctly when teradata is insatlled under UNIX platform.

To test what's wrong, I write two SP, the two SP's function is same, but result is different, my teradata server's OS is Windows 2000.

First Procedure:

REPLACE PROCEDURE sp_test1 (IN chM1YMD CHAR(8))
BEGIN
DECLARE dateM1,dateM2 DATE;
DECLARE chM2YM CHAR(6);
SET dateM1=CAST(chM1YMD AS DATE FORMAT 'YYYYMMDD');
SET dateM2=ADD_MONTHS(dateM1,-1);
SET chM2YM = SUBSTRING( CAST(CAST(dateM2 AS  FORMAT 'YYYYMMDD') AS
char(8))
FROM 1 FOR 6);
END

call sp_test1('20030901');

The result message is
Code = 2665.
2665:  sp_test:Invalid date.d transaction ABORT.

Second Procedure:

REPLACE PROCEDURE sp_test2 (IN chM1YMD CHAR(8))
BEGIN
DECLARE chM2YM CHAR(6);
SET chM2YM = SUBSTR(
                    CAST(
                         CAST(
                              ADD_MONTHS(CAST(chM1YMD AS DATE
FORMAT 'YYYYMMDD'),-1)
                              AS  FORMAT 'YYYYMMDD')
                         AS char(8)),
                        1, 6);
PRINT chM2YM;
END

call sp_test2('20030901');

It run correctly and the print information 200308 which is what I want.

I use Teradata SQL Assistant v6. I also checked my ODBC configuration, the session mode is Teradata, datetime format is AAA, that is, date, time and timestamp format are both ANSI. If I change datetime format to III, that is, date, time and timestamp format are both integer. I got the same results.

Who can tell me what's wrong?

Many thanks.

Zheng Hongxia
Beijing Smarteam Technology Co., Ltd.
China

	<Prev	Next>		<<First	<Prev	Next>	Last>>