Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
my stored procedure is as follows:
ALTER procedure [dbo].[spuserdetails]
@Name varchar(50),
@DOB datetime,
@Age int=null,
@Address1 varchar(70),
@phone int,
@Country varchar(50) = null,
@States Varchar(50) = null,
@City varchar(50) = null

As 
Begin
insert into userdetails(Name,DOB,Addr,phn,Country,States,City)
values (@Name,@DOB , @Address1 , @phone , @Country, @States, @City)
end


when i execute it :
exec  spuserdetails 'smasher','23-12-1989' ,'516-A wills street',761456,'India','kerala','Tri',1


the following error occurs when i execute stored procedure:
Msg 8114, Level 16, State 5, Procedure spuserdetails, Line 0
Error converting data type varchar to datetime.

why? how to pass a datetime parameter in table?
Posted
Updated 15-Jul-20 7:21am

Swap your date parameter around so that it's 'yyyy-mm-dd' eg: '1989-12-23'
 
Share this answer
 
Comments
Rambo_Raja 26-Jun-13 2:56am    
'1989-12-23' changing as u said also dont works.same error.
_Damian S_ 26-Jun-13 2:59am    
Your exec sp... has the parameters in the wrong order. They need to match the order specified in the parameter list.
Rambo_Raja 26-Jun-13 3:11am    
thnx..solved;)
Nabeel SherSoft 10-Jul-16 9:39am    
same error
Another way that doesn't involve messing around with strings:
SQL
declare @dt as datetime = datetimefromparts(2016,1,25,17,0,0,0)

EXEC	exec  spuserdetails 'smasher', @dt,'516-A wills street',761456,'India','kerala','Tri',1

When calling sp from ADO.NET, need to specify size (bytes) for date parameter (eg datetime2(0) = 6, refer to MSDN info for sizes of different types of date).
 
Share this answer
 
Comments
CHill60 31-Jan-16 8:36am    
Question is over 2 years old and already answered.
Also, to be pedantic, '1989-12-23' wouldn't be a string - it's a datetime.
If you are going to suggest the use of a new in-built function it's worth mentioning that the function wasn't introduced until SQL Server 2012 and it doesn't work with the Express version of 2012.
Not sure of the relevance of talking about ADO - as SQL Server has been tagged it would make more sense to use SqlParameter, I would only use datetime2 if you need the extra precision going back to the 1700's. I've never passed the size for date parameters.
you have to pass like MM-DD-YYYY (ex:12-01-2010) or YYYY-MM-DD
 
Share this answer
 
v2
Comments
CHill60 26-Jun-13 9:12am    
Best to avoid country specific formats (MM-DD-YYYY would be incorrect for the UK for example). Stick to ISO 8601 http://www.w3.org/QA/Tips/iso-date[^]
Change you date value as below

exec  spuserdetails 'smasher','1989-12-23' ,'516-A wills street',761456,'India','kerala','Tri',1
 
Share this answer
 
I could not pass datetime parameter to a stored procedure, where datatime parameter was being used in dynamic sql query, the lesson I have learned:

1. convert the parameter to nvarchar rather than to datetime
2. use extra single inverted comma before and after conversion of the datetime parameter to nvarchar
3. Using the above two steps, our target is to achieve the date like this, APPL_ApplicationDate <='27-jan-2015'.
SQL
WHERE APPL_ApplicationDate <=  '''+CONVERT(nvarchar, @RefDate)+'''



Solution:
SQL
WHERE APPL_ApplicationDate <=  '''+CONVERT(nvarchar, @RefDate)+'''
set @SqlCommand=N'
WITH ctePivotData AS
(
       select CMPN_Company_Name, APPL_Student_Status, STUD_Student_ID       from ng.dbo.STUDstudent
       INNER JOIN ng.dbo.CMPN_Company_main
              ON STUDstudent.STUD_School_ISN=CMPN_Company_main.CMPN_ISN
       INNER JOIN ng.dbo.APPLications
              ON STUDstudent.STUD_Student_ID=APPLications.APPL_Student_ID
       WHERE APPL_ApplicationDate <=  '''+CONVERT(nvarchar, @RefDate)+'''
)
SELECT CMPN_Company_Name,' + @SpreadingList + ' INTO #PivotTable
FROM ctePivotData
PIVOT(count(STUD_Student_ID) FOR APPL_Student_Status IN (' + @SpreadingList + ') ) AS P
order by CMPN_Company_Name;

select * from #PivotTable;';

--print @SqlCommand

execute sp_executesql @SqlCommand;
 
Share this answer
 
v2
Comments
CHill60 30-Jan-15 6:11am    
Somewhat off-topic for this question - this answers the un-asked question "how do I use a datetime variable in dynamic sql" .. and the actual question is quite old and already resolved.
eliminate string and localization problems with passing float values

exec myproc @param=cast(getdate() as float)

procedure myproc
@param float
as
declare @mydate datetime
select @mydate = cast(@param as datetime)
 
Share this answer
 
Hi, The problem is with the data type. You have to mention it as @date instead of @ datetime. When it is @datetime, you are expected to add the time along with the date. So, change the data type in the main query while you create the procedure.
 
Share this answer
 
Comments
Richard MacCutchan 15-Jul-20 13:23pm    
This question was resolved seven years ago. Please focus on current open questions.
CHill60 16-Jul-20 4:33am    
Incorrect.
If you declare a datetime variable and only assign the date part, the time is automatically set to midnight. E.g.
declare @demo datetime = '2020-07-16' 
select @demo
returns 2020-07-16 00:00:00.000

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900