SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Loading SAS datetime into Teradata timestamp works but returns an error

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Loading SAS datetime into Teradata timestamp works but returns an error

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;


Accepted Solutions
Solution
‎08-28-2017 10:59 AM
Respected Advisor
Posts: 4,675

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

[ Edited ]
Posted in reply to bentleyj1

@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


All Replies
PROC Star
Posts: 503

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

Posted in reply to bentleyj1
Contributor
Posts: 69

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

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.

PROC Star
Posts: 503

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

Posted in reply to bentleyj1

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.

Solution
‎08-28-2017 10:59 AM
Respected Advisor
Posts: 4,675

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

[ Edited ]
Posted in reply to bentleyj1

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

Contributor
Posts: 69

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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