Click here to Skip to main content
14,768,479 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

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

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?
Updated 15-Jul-20 8:21am

Swap your date parameter around so that it's 'yyyy-mm-dd' eg: '1989-12-23'
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
Nabeel SherSoft 10-Jul-16 9:39am
same error
you have to pass like MM-DD-YYYY (ex:12-01-2010) or YYYY-MM-DD
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[^]
Change you date value as below

exec  spuserdetails 'smasher','1989-12-23' ,'516-A wills street',761456,'India','kerala','Tri',1
Another way that doesn't involve messing around with strings:
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).
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.
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'.
WHERE APPL_ApplicationDate <=  '''+CONVERT(nvarchar, @RefDate)+'''

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;
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
declare @mydate datetime
select @mydate = cast(@param as datetime)
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.
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
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