Set GETDATE as a default parameter value in stored procedure in SQL Server

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Error Conversion failed when converting date andor time from character string.

i have SP and date column type is date, i want to use current date in store procedure

i am not passing any value and i will not pass any value, i am using current date by using getdate()

Alter PROCEDURE Sp_Manual 
	-- Add the parameters for the stored procedure here
	@StartDate date =GETDATE,
	@Enddate   date =GETDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT l.LocName AS Location, SUM(c.QTY) AS Expr1, SUM(c.IWeight) AS Weigth
	FROM ConIssuance AS c 
	INNER JOIN Location AS l ON c.LocID = l.LocID                       
	where C.EntryDate between @StartDate and @Enddate
	GROUP BY c.QTY, c.IWeight, l.LocName 
END
GO

 

Posted 5 months ago Modified on 5 months ago

If you are not going to pass the parameter then why are you making procedure parameterised.

Simply pass GETDATE() to where condition.

WHERE C.EntryDate BETWEEN CONVERT(DATE, GETDATE()) AND CONVERT(DATE, GETDATE())

Still if you want then

You can not use GETDATE() as a default parameter value. SET default as NULL and replacing that NULL in your code with GETDATE().