insert date macro variable into teradata

[csetzkorn]

I have a column defined as follows in a TeraData table:

RunDay DATE FORMAT 'YYYY-MM-DD'

I try to insert one row into this table as follows given a macro variable RunDay:

%let RunDay = %sysfunc(datetime());

proc sql noerrorstop;
	connect to teradata(User=&user. password=&passwd. mode=teradata);
	Execute(
		INSERT INTO SomeGreatTable
		(
			RunDay
		)
		VALUES 
		(
			input("&RunDay.",YYMMDD.)
		);
	);
)By Teradata;
quit;

Any ideas why this does not work? Thanks!

[RW9]

What does not work?

Look at what you are doing:

%let RunDay = = %sysfunc(datetime());
input("&RunDay.",YYMMDD.)

Now de-refeference the macro variable:

input(" = %sysfunc(datetime())",YYMMDD.)

Does that look correct to you?  No, even if you fix the double = sign, the %sysfunc() will return a number - which is how SAS stores dates/times/datetimes.  This is basic SAS stuff which you should know before trying to do macros.

Something like:

%let RunDay=%sysfunc(datetime());

put(datepart("&RunDay."),YYMMDD.)

Should work, however not tested.

[Kurt_Bremser]

Slight modification:

put(datepart(&RunDay.),YYMMDD.)

as &RunDay will be (and should be) a numeric value.

[csetzkorn]

Thanks but it does not: ERROR: Teradata execute: Syntax error: Data Type "datepart" does not match a Defined Type name.

[Tom]

If you are pushing SQL code into Teradata to execute it needs to be valid Teradata SQL code. That means you can't use the INPUT() function.  Even if you could your macro variable was not formatted in the form that your INPUT statement was trying to read.  Try making your macro variable have the content '2017-02-14' instead. Note to get a DATE value you want to use the DATE() function and not the DATETIME() function.

%let RunDay = %unquote(%bquote('%sysfunc(date(),yymmddd10)'));

proc sql noerrorstop;
	connect to teradata(User=&user. password=&passwd. mode=teradata);
	Execute(
		INSERT INTO SomeGreatTable
		(
			RunDay
		)
		VALUES 
		(
			&RunDay 
		);
	);
)By Teradata;
quit;

[csetzkorn]

Thanks Tom. I am getting: ERROR: Teradata execute: A character string failed conversion to a numeric value.

[Tom]

Probably because the macro variable was generated using the DATETIME() function instead of the DATE() function. That would probably cause the YYMMDDD10. format to generate *********************.  Let me update my answer above to correct that.

[csetzkorn]

It appears that this:

%let RunDay = 2016-10-01;

and this works:

CAST(%bquote('&RunDay.') AS DATE FORMAT 'YYYY-MM-DD')

Last hurdle is to inialise the macro variable with the current run day (rather than hardcoding it).

[Shmuel]

@Tom,  you probably meant: 

%let RunDay = %unquote(%bquote('%sysfunc(today(),yymmddd10)'));

 or maybe you need take of the quotes:

%let RunDay = %unquote(%bquote(%sysfunc(today(),yymmddd10)));

[Tom]

Yes. The original request used DATETIME() instead of DATE().  I updated my post to fix that.

Note the TODAY() is just an alias for the DATE() function.