how to pass a datetime parameter while executing stored procedure.

Question

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 25-Jun-13 20:44pm

Rambo_Raja

Updated 15-Jul-20 7:21am

Add a Solution

7 solutions

Solution 5

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).

Posted 30-Jan-16 17:05pm

bangkokrat

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.

Solution 2

you have to pass like MM-DD-YYYY (ex:12-01-2010) or YYYY-MM-DD

Posted 25-Jun-13 20:50pm

Member 8362403

Updated 25-Jun-13 20:51pm

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[^]

Solution 3

Change you date value as below

exec  spuserdetails 'smasher','1989-12-23' ,'516-A wills street',761456,'India','kerala','Tri',1

Posted 25-Jun-13 20:53pm

Radhadeep12

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'.

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;

Posted 29-Jan-15 6:07am

Masud Ahmed

Updated 29-Jan-15 6:09am

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.

Solution 6

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)

Posted 1-Apr-16 13:41pm

Member 12431433

Solution 8

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.

Posted 15-Jul-20 7:21am

Member 14890430

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

Add a Solution

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

_Damian S_ · Accepted Answer · 2013-06-25T20:47:00

Solution 1

Swap your date parameter around so that it's 'yyyy-mm-dd' eg: '1989-12-23'

Posted 25-Jun-13 20:47pm

_Damian S_

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

how to pass a datetime parameter while executing stored procedure.

7 solutions

Solution 1

Solution 5

Solution 2

Solution 3

Solution 4

Solution 6

Solution 8

Add your solution here

Preview 0

how to pass a datetime parameter while executing stored procedure.

7 solutions

Solution 1

Solution 5

Solution 2

Solution 3

Solution 4

Solution 6

Solution 8

Add your solution here

Preview 0

Existing Members

...or Join us