Inserting SAS datetime into Teradata timestamp field using proc sql

[SebastianA]

Hi All,

I am trying to store a datetime value from SQL server in a SAS macro-variable which I am eventually updating into a teradata timestamp(6) field using a proc sql statement.

My data is present in the SAS macro-variable (&MY_MACRO_VAR) as 03MAR2021:10:26:21.987.

I am trying to update the teradata field using the below proc sql.

PROC SQL NOPRINT;
CONNECT TO teradata (user=Terauser password=Terapass tdpid=dbc);
EXECUTE (
UPDATE MY_TERADATA_TABLE
SET MY_DTTM = CAST(%BQUOTE('&MY_MACRO_VAR') AS TIMESTAMP(0)
FORMAT 'DDMMMYYYY:HH:MI:SS.SSS')
) BY teradata;
EXECUTE (COMMIT) BY teradata;
DISCONNECT FROM teradata;
QUIT;

However, I am getting - ERROR: Teradata execute: Invalid FORMAT string 'DDMMMYYYY:HH:MI:SS.SSS'.

The existing data present in teradata timestamp field is - 3/02/21 09:15:35.477000.

Could anyone help please?

[SASKiwi]

I'm guessing you need to adjust the CAST format from FORMAT 'DDMMMYYYY:HH:MI:SS.SSS' to FORMAT 'DDMMYY:HH:MI:SS.SSS' although I can't tell from what you have posted whether month or day comes first. If month comes first swap the DD and MM around.

[SebastianA]

@SASKiwi  Thanks for the response.

It is mm/dd/yy format in teradata.

Data in source SQL server - 2021-03-03 10:26:21.987

Data in macro-var &MY_MACRO_VAR  - 03MAR2021:10:26:21.987

Data existing in Target teradata - 3/01/21 09:15:35.477000

I tried it as the way you suggested.

UPDATE MY_TERADATA_TABLE
SET MY_DTTM = CAST(%BQUOTE('&MY_MACRO_VAR.') AS TIMESTAMP(6)
FORMAT 'MMDDYY:HH:MI:SS.SSS')

I am also formatting it before the proc sql.

data _null_;
format MY_DTTM DATETIME19.;
run;

Still it is throwing error.

ERROR: Teradata execute: Invalid FORMAT string 'MMDDYY:HH:MI:SS.SSS'.

[SASKiwi]

What happens if you test a hard-coded value without the CAST?

UPDATE MY_TERADATA_TABLE
SET MY_DTTM = '3/01/21 09:15:35.477'

[SebastianA]

I tried and getting the same error.
ERROR: Teradata execute: Invalid timestamp.

[SASKiwi · Accepted solution]

According to the Teradata documentation the default timestamp format is this: YYYY-MM-DD HH:MI:SS.S.

Try changing your hard-coded timestamp string to match this and test it again.

BTW I've never used Teradata and I guess you can lookup the appropriate Teradata docs just as easy as I can....

[SebastianA]

I fetched the formatted datetime field from source SQL server and used that to update the field in Teradata which resolved the issue.

CONNECT TO SQLSVR (user=&Sqluser. password=&Sqlpass. datasrc=SQL_SRV ReadBuff=32767);
SELECT MY_DTTM into :MY_MACRO_VAR FROM connection to SQLSVR
(
select FORMAT(CAST([DATE] AS DATETIME2(6)),'yyyy-MM-dd hh:mm:ss.ssssss') AS
[MY_DTTM] FROM [dbo].[CONTROL_TABLE]
);
DISCONNECT FROM SQLSVR;
QUIT;

PROC SQL NOPRINT;
CONNECT TO teradata (user=&Terauser password=&Terapass tdpid=dbc);
EXECUTE (
UPDATE MY_TERADATA_TABLE
SET MY_DTTM = CAST(%BQUOTE('&MY_MACRO_VAR.') AS TIMESTAMP(6)
FORMAT 'MMDDYY:HH:MI:SS.SSS')
) BY teradata;
EXECUTE (COMMIT) BY teradata;
DISCONNECT FROM teradata;
QUIT;