Click here to Skip to main content
14,485,088 members
Rate this:
Please Sign up or sign in to vote.
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 17-Apr-19 3:19am
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
Rate this:
Please Sign up or sign in to vote.

Solution 2

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[^]
Rate this:
Please Sign up or sign in to vote.

Solution 3

Change you date value as below

exec  spuserdetails 'smasher','1989-12-23' ,'516-A wills street',761456,'India','kerala','Tri',1
Rate this:
Please Sign up or sign in to vote.

Solution 5

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.
Rate this:
Please Sign up or sign in to vote.

Solution 6

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)
Rate this:
Please Sign up or sign in to vote.

Solution 4

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.
Rate this:
Please Sign up or sign in to vote.

Solution 7

Please follow the Accepted answer from the below link

sql server 2008 - Type datetime for input parameter in procedure - Stack Overflow[^]

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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100