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?
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....
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.
@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'.
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'
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....
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.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.