Datetime in a SQL Passthrough

[aj34321]

Hi All, 

Request if someone can help me on the below error. I'm simply storing start & end time in a macro variable and passing it into an SQL passthrough to update the start and end times into sql table. What should i change it, so that the table is updated for the start and end times.

ERROR: CLI execute error: [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Arithmetic overflow
error converting expression to data type datetime. : [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol
driver][Microsoft SQL Server]The statement has been terminated.

%let transfer_start_time = %sysfunc(datetime());
/*sas code here*/
%let transfer_end_time = %sysfunc(datetime());

proc sql;
connect to sqlsvr (&sqlconn_stg);
execute
( update schema.table
set
StartDate = &transfer_start_time,
EndDate = &transfer_end_time,
RecordCount = 10000,
StatusCode = 'X1'
where
TransferId = 1010 and
StatusCode = 'XP'
) by sqlsvr;
disconnect from sqlsvr;
quit;

Thanks,

Anil

[Patrick]

The code in your pass-through block must be valid SQL Srv syntax. The SAS macro variable must therefore resolve to a string which is such valid syntax.

set
StartDate = &transfer_start_time,
EndDate = &transfer_end_time,

What strings do above two macro variable contain - and is it valid SQL Srv syntax?

[aj34321]

Hi Patrick,

Below is what im assigning into macro variable.

%let transfer_start_time = %sysfunc(datetime());
/*sas code here*/
%let transfer_end_time = %sysfunc(datetime());

Thanks,

Anil

[Reeza]

Check the value of those macro variables. Do they look exactly like what you would hard code in MS SQL server? I highly suspect not. 

The key to macro code is make it work first without macro variables and then create the macro portions necessary. 

[Patrick]

@aj34321

Code like: %let transfer_start_time = %sysfunc(datetime()); populates SAS macro variable &transfer_start_time with a SAS DateTime value. Right now when I run it this value would be: 1799530675.664

Each database deals with Dates, DateTimes and TimeStamps differently. You can't just pass in a SAS DateTime value in a pass-through SQL block (which is in data base native SQL) as then the database doesn't have any information how to convert this SAS value to the database specific representation.

You need either to look up how to pass in a STRING - and populate the SAS Macro variable with such a string - or if you just want to pass in the system time equivalent of what you would get with the SAS Datetime() function then use the equivalant database function in your pass-through SQL block. For a SQL Server DateTime column this appears to be function  SYSDATETIME() https://msdn.microsoft.com/en-us/library/bb630353.aspx

I haven't worked a lot with SQL Server so I don't know if function SYSDATETIME() will resolve for every single row or only once. I'm sure SQL Server has implemented options for both cases and it's just a question of RTM to figure out the right syntax.