date macro variable in explicit pass through sql

[csetzkorn]

This Sql work fine:

SELECT 
 TOP 10 * 
FROM SomeCalendarData
WHERE SomeDate = '2016/01/01';

So using a 'string macro variable' like so should work as well:

%let StartDate = 2016/01/01;

proc sql;
   connect to teradata 
    (BULKLOAD=YES MODE=TERADATA user=&user. Password=&passwd.);

   CREATE TABLE WorkTest.test AS
   select * from connection to teradata
   ( 
		SELECT 
			TOP 10 *      
		FROM SomeCalendarData
		WHERE SomeDate = %bquote('&StartDate.');
   ); 
quit;

Any idea why this is not the case? Thanks!

[RW9]

This line:

		WHERE SomeDate = %bquote('&StartDate.');

Your macro variable will not resolve unless its in doubel quotes:

		WHERE SomeDate = %bquote("&StartDate.");

[csetzkorn]

Sorry this does not work as terradata does not like the issued sql.

[SASKiwi]

If Teradata doesn't like double quotes try this:

WHERE SomeDate = %str(%')&StartDate.%str(%');

[csetzkorn]

After this I am getting: ERROR: Teradata prepare: A character string failed conversion to a numeric value. SQL statement was: SELECT TOP 10 * FROM
SomeCalendarData WHERE SomeDate = '2016/01/01';. This is a bit confusing as SELECT TOP 10 * FROM
SomeCalendarData WHERE SomeDate = '2016/01/01'; works fine in sql assistent when run against teradata. Any ideas?

[SASKiwi]

I've never used Teradata but a quick Google search for Teradata date formats suggests '2016-01-01' is more commonly used. Have you tried that?

[csetzkorn]

As i said: SELECT TOP 10 * FROM
SomeCalendarData WHERE SomeDate = '2016/01/01'; returns the expected results when I issue it from another teradata client (sql assistent) so I am a bit lost what's going on. Maybe SAS's explicit tries to be clever whilst causing issues.

[csetzkorn · Accepted solution]

Thanks for all your help, This:

%let StartDate = 2016/01/01;

proc sql;
   connect to teradata 
    (BULKLOAD=YES MODE=TERADATA user=&user. Password=&passwd.);

   CREATE TABLE WorkTest.test AS
   select * from connection to teradata
   ( 
		SELECT 
			TOP 10 *      
		FROM SomeCalendarData
		WHERE SomeDate = CAST(%bquote('&StartDate.') AS DATE FORMAT 'YYYY-MM-DD');
   ); 
quit;

Does the trick. Although I am still unsure why an explicit CAST like this should be necessary?! Bizare ... 

[Patrick]

@csetzkorn

Although I am still unsure why an explicit CAST like this should be necessary?

According to docu that's the syntax:

CAST ( expression AS data_type [ ( length ) ] )

 https://msdn.microsoft.com/en-AU/library/ms187928.aspx#BKMK_examples

If you just use the date string without any cast, then are you sure that this string gets converted to a SQL Server representation of DATE and not of DATETIME2 due to Data Type Precedence?

https://msdn.microsoft.com/en-us/library/ms190309.aspx

I'd assume that if it becomes DATETIME2 then your code executes without any error but the where clause doesn't behave as you'd expected it.