Error converting data type varchar to datetime in Stored Procedure

Transact-SQL
1

Hi Experts ,
I am facing a issue error message converting data type varchar to datetime while inserting the data via stored procedure, If i directly passed the dates below area it works insert the data into tables ,
EXEC Db.[dbo].[StoredProc1] '1-NOV-2021' ,'30-NOV-2021'
but when i try to use ```
EXEC Db.[dbo].[StoredProc1] '@StartDate' ,'@EndDate'

Getting error message
    Msg 8114, Level 16, State 1, Procedure Db.[dbo].[StoredProc1], Line 0 [Batch Start Line 122]
    Error converting data type varchar to datetime.
    Msg 8114, Level 16, State 1, Procedure Db.[dbo].[StoredProc2], Line 0 [Batch Start Line 122]
    Error converting data type varchar to datetime.

 Create or alter Procedure SP_InsertInto_Tables
	--																@StartDate datetime output,
	--																@EndDate datetime output
 ----@StartDate DATETIME,
 ----@EndDate DATETIME
AS
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = dateadd(month,0+datediff(month,0,'1-Nov-2021'),0)  
SET @EndDate =  dateadd(month,1+datediff(month,0,getdate()),-1) 
begin
INSERT INTO [Codelist].[dbo].[Table1]
--EXEC Db.[dbo].[StoredProc1] '1-NOV-2021' ,'30-NOV-2021'
EXEC Db.[dbo].[StoredProc1] '@StartDate' ,'@EndDate'


INSERT INTO [Codelist].[dbo].[Table2]
--EXEC Db.[dbo].[StoredProc2]  '1-NOV-2021' ,'30-NOV-2021
EXEC Db.[dbo].[StoredProc2]  '@StartDate' ,'@EndDate'

	end
2

You don't put the parameters in quotes. Remove the single quotes from around the parameters.

EXEC Db.[dbo].[StoredProc2] @StartDate , @EndDate

When you use variables for the parameters you don't put them in quotes, you just use the parameters and the correct way will be interpreted by the engine.

2 Likes
3

Thank you ! Working :relaxed: