BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bentleyj1
Quartz | Level 8

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:

timestamp.jpg 

 

 

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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 **** 

View solution in original post

5 REPLIES 5
bentleyj1
Quartz | Level 8

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.

kiranv_
Rhodochrosite | Level 12

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.

Patrick
Opal | Level 21

@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 **** 

bentleyj1
Quartz | Level 8

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 4513 views
  • 3 likes
  • 3 in conversation