passing date as a parameter in a procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

passing date as a parameter in a procedure

Author

Topic

mdanwerali
Starting Member

30 Posts

Posted - 2002-10-30 : 05:01:18

hi,

I am passing a date as a parameter but it is giving an error...

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

my actual procedure is :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure testing
(
@Date datetime,
@empid int
)
As
Select jobmno,ejfrom from empjobs where ejfrom = @Date
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

and i am passing the date value as :

DECLARE @RC int
DECLARE @Date datetime
DECLARE @empid int
SELECT @Date = '16-04-2002'
SELECT @empid = 1

EXEC @RC = [corp].[sqldb].[testing] @Date, @empid
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: corp.sqldb.testing'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

can u please tell me how to pass solve this....

Anwer

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-30 : 05:14:25

quote:

hi,

I am passing a date as a parameter but it is giving an error...

Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

my actual procedure is :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE Procedure testing
(
@Date datetime,
@empid int
)
As
Select jobmno,ejfrom from empjobs where ejfrom = @Date
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

and i am passing the date value as :

DECLARE @RC int
DECLARE @Date datetime
DECLARE @empid int
SELECT @Date = '16-04-2002'
SELECT @empid = 1

EXEC @RC = [corp].[sqldb].[testing] @Date, @empid
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: corp.sqldb.testing'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

can u please tell me how to pass solve this....

Anwer

Change the @date bit where you call the procedure to

CONVERT (datetime,@date,103) or use a US date format.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-30 : 05:15:07

Is this a cross server call?
If so the datetime will be converted to character then back again.
If the two servers are set up differently (or I believe the same in some cases) it can get an error.

try with yyyymmdd that should work in all cases.
If you look at the query plan you can see what is sent.

DECLARE @RC int
DECLARE @Date varchar(8)
DECLARE @empid int
SELECT @Date = '20020416'
SELECT @empid = 1

EXEC @RC = [corp].[sqldb].[testing] @Date, @empid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 10/30/2002 05:15:55

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-30 : 05:15:54

Sorry I mean where you select the date

SELECT @Date = convert (datetime,'16-04-2002',103)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-10-30 : 05:35:12

Personally....i've always found it easiest to pass dates into stored procedure as text strings...and then work manipulate them into whatever form I need inside the SP.

It is also advisable to be SPECIFIC in your dateformats....(forum) search here for "SET DATEFORMAT" for previous discussions on this topic.

(One of the "extra-challenges in life" us non-US people encounter is that defaults (values,formats,settings,etc) in the M$ world are usually American....it's just a case of recognising them and then adapt to live with them.)

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-10-30 : 08:17:02

You are passing the date in this format "mm-dd-yy" (110), you should convert it to datetime 105 format "dd-mm-yy".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources