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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3815 views
  • 3 likes
  • 3 in conversation