topic Re: Loading SAS datetime into Teradata timestamp works but returns an error in SAS Data Management

Loading SAS datetime into Teradata timestamp works but returns an error

bentleyj1 — Wed, 23 Aug 2017 20:07:18 GMT

I need to insert a value into a Teradata table that has a timestamp( 6) field named trigger_timestamp. I’m deriving a SAS datetime macro variable named _triggerRunDt.

Loading &_triggerRunDt into trigger_timestamp returns the error ‘Teradata execute: Invalid operation for DateTime or Interval’ but the value is loaded correctly:

Teradata documentation has this explanation for the error message:

5407 Invalid operation for DateTime or Interval.

Explanation: An operation that requires an appropriate DateTime or Interval data type or value, and it does not.

Can anyone suggest how to get rid of the error? I don’t have a problem loading Teradata date fields, only timestamps. Here are the relevant sections from the log. Thanks in advance.

MPRINT(ONE): libname refTabls teradata user="GIB_TEST" password="{SAS002}9B8C3F4E4E42CE1D1693C5F648AE11641F188708"

server='ROCK.GIBRALTER.COM' database=MAIN mode=ansi connection_group=cefGroup dbsaslabel=none dbsliceparm=(none);

NOTE: Libref REFTABLS was successfully assigned as follows:

Engine: TERADATA

Physical Name: ROCK.GIBRALTER.COM

MPRINT(RUN_CREATE): ;

MPRINT(RUN_CREATE): data _null_;

MPRINT(RUN_CREATE): triggerRunDt=DHMS(today(),hour(time()),minute(time()),second(time()))+60*60*5;

MPRINT(RUN_CREATE): call symputx('_triggerRunDt',triggerRunDt);

MPRINT(RUN_CREATE): format triggerRunDt datetime.;

MPRINT(RUN_CREATE): put _all_;

MPRINT(RUN_CREATE): run;

triggerRunDt=23AUG17:18:10:09 _ERROR_=0 _N_=1

_TRIGGERRUNDT=1819131009.2

MPRINT(RUN_CREATE): proc sql;

MPRINT(RUN_CREATE): insert into refTabls.PROCESS_TRIGGER (cmpn_cd, cmpn_type_cd, prod_test_cd, process, analyst_id, email_list_id_1 , source_schema, source_object, trigger_timestamp, delayed_process, delayed_suffix) values("600210", "HEP", 'TEST', 'CMPN_CREATE' , "bullwinkle@home.com", "rocky@home.com'", "DT_D_CHESS", "P10081_LOAD_TABLE", 1819131009, 'NA', 'NA');

ERROR: Teradata execute: Invalid operation for DateTime or Interval.

NOTE: The following 1 column(s) in REFTABLS.WFPDMHL_CEF_PROCESS_TRIGGER have been automatically dropped because they have a datatype that is not supported by this engine:

delayed_output_list

NOTE: 1 row was inserted into REFTABLS.WFPDMHL_CEF_PROCESS_TRIGGER.

MPRINT(RUN_CREATE): quit;

Re: Loading SAS datetime into Teradata timestamp works but returns an error

kiranv_ — Wed, 23 Aug 2017 20:57:53 GMT

try using sasdatefmt. please check the link below

https://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371624.htm

Re: Loading SAS datetime into Teradata timestamp works but returns an error

bentleyj1 — Wed, 23 Aug 2017 22:12:27 GMT

Thanks for the suggestion kiranv. I saw this info about the SASDATEFMT= option already and I figured it doesn't apply to my value--it's already a numeric datetime value. But maybe I'm getting thrown off because in their INSERT example they're using a a datetime literal.

I'll give it a try.

Re: Loading SAS datetime into Teradata timestamp works but returns an error

kiranv_ — Wed, 23 Aug 2017 22:33:41 GMT

Numeric value of datetime in SAS and Teradata values will be entirely different, as both of them store date entirely different manner. I am not sure why would like to insert numeric date values. It would be always safer, clearner to use them as datetime values.

Re: Loading SAS datetime into Teradata timestamp works but returns an error

Patrick — Wed, 23 Aug 2017 22:58:41 GMT

@bentleyj1

You definitely need to pass in the datetime value as a datetime string and not as a SAS specific number for a datetime.

It just needs to be something which informs the SAS/Access engine that its dealing with a SAS Datime value so that it converts it to a target database specific value.

One option: "23AUG17:18:10:09" dt

PROC sql;

INSERT INTO refTabls.PROCESS_TRIGGER (
	cmpn_cd
	,cmpn_type_cd
	,prod_test_cd
	,process
	,analyst_id
	,email_list_id_1
	,source_schema
	,source_object
	,trigger_timestamp
	,delayed_process
	,delayed_suffix
	)
VALUES (
	"600210"
	,"HEP"
	,'TEST'
	,'CMPN_CREATE'
	,"bullwinkle@home.com"
	,"rocky@home.com'"
	,"DT_D_CHESS"
	,"P10081_LOAD_TABLE"
	,"23AUG17:18:10:09"dt
	,'NA'
	,'NA'
	);

quit;

P.S: I strongly recommend to not post or obfuscate things like credentials. Just replace these bits with ****

Re: Loading SAS datetime into Teradata timestamp works but returns an error

bentleyj1 — Mon, 28 Aug 2017 15:03:48 GMT

I wasn't able to get sasdatefm= for my query. I'm inserting macro variables and literals so I wasn't selecting from a data source so there was no where to place the sasdatefmt. I could have rewritten it to use a dummy data soure but instead used a datetime literal. Thanks for the suggestion though. I certainly remember this approach.