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:
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;
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 ****
try using sasdatefmt. please check the link below
https://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371624.htm
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.
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.
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 ****
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.