MSSQL – DATEADD value in parameters is returning an error

byMR

November 14, 2022

I’ve built a procedure I need to run it for the last 60 days. I can’t seem to figure it out.

I’ve got the following date parameters statements for the parameters and they return

select dateadd(Day, -60, getdate()), dateadd(Day, -1, getdate())

The code itself adjusts the end date to be at the very end of the day. 23:59:59.

When I do this

exec LRP_PROC 
@start_Dt = dateadd(Day, -60, getdate()),
@end_dt = dateadd(Day, -1, getdate()),
@mode = 0

I get the following error

I can’t figure out what i’m doing wrong.
Thank you.

>Solution :

SQL Server does not permit arbitrary expressions to be passed as stored procedure parameters. You need to declare and assign local variables before calling the proc.

declare @start_Dt datetime = dateadd(Day, -60, getdate());
declare @end_dt datetime = dateadd(Day, -1, getdate());

exec LRP_PROC @start_Dt, @end_dt, @mode = 0;