passing Datetime from Exec Proc_Name error

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 2005 Forums

Transact-SQL (2005)

passing Datetime from Exec Proc_Name error

Author

Topic

profiler
Starting Member

7 Posts

Posted - 2010-01-13 : 06:19:30

Hello Can somebody suggest me
I am getting this error

Conversion failed when converting datetime from character string.

Alter Procedure [dbo].[Test_Search]
(
@Date DateTime
)
AS
Declare
@Query nvarchar(4000),
select @Query=''
Begin

Set @Query='Select * from search where CreatedDate<='+(@Date)

end

exec [Test_Search] '3/11/2009'

It is giving me error saying Conversion failed when converting datetime from character string.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 06:22:22

firstly staement should be
Set @Query='Select * from search where CreatedDate<=''' + @Date + ''''
also pass date in iso format
exec [Test_Search] '2009-11-03'

profiler
Starting Member

7 Posts

Posted - 2010-01-13 : 06:36:26

getting the same error

Alter Procedure [dbo].[Test_Search]
(
@Date DateTime
)
AS
Declare
@Query nvarchar(4000)
Begin
Set @Query=''
Set @Query='Select * from search where CreationDate<='''+(@Date)+''''

end
exec @Query

exec [Test_Search] '2009-11-03'
this is how i did.

Sachin.Nand

2937 Posts

Posted - 2010-01-13 : 06:52:18

Declare the @Date variable as varchar & try.

PBUH

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-13 : 07:00:14

DECLARE @Query NVARCHAR(4000),@Date DATETIME

SELECT @Date = '2009-11-03'

SET @Query=''
SET @Query='SELECT * from search where CreationDate <='+CONVERT(VARCHAR(32),@Date,103)+''

PRINT @Query
EXEC(@Query)

profiler
Starting Member

7 Posts

Posted - 2010-01-13 : 07:04:23

its working in sql using varchar but when i am passing it from my asp.net application it is giving error.

string dt = "2009-11-03";

this is how i have declared my dt variable.

Sachin.Nand

2937 Posts

Posted - 2010-01-13 : 07:06:53

quote:
Originally posted by profiler

its working in sql using varchar but when i am passing it from my asp.net application it is giving error.

string dt = "2009-11-03";

this is how i have declared my dt variable.

Then use bklr approach.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 07:32:16

quote:
Originally posted by profiler

its working in sql using varchar but when i am passing it from my asp.net application it is giving error.

string dt = "2009-11-03";

this is how i have declared my dt variable.

what happens when you use datetime?

profiler
Starting Member

7 Posts

Posted - 2010-01-13 : 07:44:13

thats giving me error...

Then use bklr approach.

i dont know what is bklr??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 07:47:36

quote:
Originally posted by profiler

thats giving me error...

Then use bklr approach.

i dont know what is bklr??

whats the format in which application sents dates to SQL server? you may run a trace to see how value is getting passed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources