SUBSTRING in Query Vs Macro

Tools & Utilities

SUBSTRING in Query Vs Macro

Hello,

When I execute a query using the SUBSTRING function, it executes fine in SQL Assistant but when I port it over to a macro it errors out:

"6758: Invalid Time"

Here's a simple setup to see what I mean:

DROP TABLE FP_BUSHOUR_REF;

CREATE SET TABLE FP_BUSHOUR_REF
(
FixedperiodId DECIMAL(10,0) NOT NULL,
Bushour SMALLINT,
Fp_Num SMALLINT,
Fp_start VARCHAR(20),
Fp_End VARCHAR(20))
UNIQUE PRIMARY INDEX ( FixedperiodId );

INSERT INTO fp_bushour_ref (96,18,73,'18:00','18:14:59');
INSERT INTO fp_bushour_ref (97,18,74,'18:15','18:29:59');
INSERT INTO fp_bushour_ref (98,18,75,'18:30','18:44:59');
INSERT INTO fp_bushour_ref (99,18,76,'18:45','18:59:59');

SELECT SUBSTRING(CAST(CAST(fp.fp_start AS TIME) AS CHAR(8)) FROM 7 FOR 2)
FROM fp_bushour_ref fp

Works OK...

CREATE MACRO test_fp_bushour_ref
AS
(
SELECT SUBSTRING(CAST(CAST(fp.fp_start AS TIME) AS CHAR(8)) FROM 7 FOR 2)
FROM fp_bushour_ref fp
;
);

EXECUTE test_fp_bushour_ref

Results in the error "6758: Invalid Time"

Any insight that can be provided is greatly appreciated.
1 REPLY

Re: SUBSTRING in Query Vs Macro

After doing some more testing and digging around, it seems this may be due to ODBC settings. This post describes a similar issue:

http://forums.teradata.com/forum/tools/error-3789-multiple-format-options-with-time-data-type