Syntax Error converting date to string

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)

Syntax Error converting date to string

Author

Topic

t1g312
Posting Yak Master

148 Posts

Posted - 2004-08-23 : 09:12:10

Hi all,

I get the error "Syntax error converting datetime from character string." when I try to execute the following SP:

CREATE Procedure xsp_SP
@p1 varchar(50),
@p2 varchar(50),
@DOB datetime
AS
Declare @SqlStr varchar(1000)
SELECT @SqlStr = '@p1,@p2,DOB from table WHERE
f1 like ''%' + @p1 + '%'' AND
COALESCE(f2,'''') like ''%' + @p2 + '%'' AND
convert(varchar,DOB,103) = convert(varchar,' + @DOB + ',103)'
execute(@SqlStr)

Please help!

Thanks in advance.

Adi

-------------------------
/me sux @sql server

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-23 : 09:19:30

Can you give an example call and the expected output?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

t1g312
Posting Yak Master

148 Posts

Posted - 2004-08-23 : 09:37:20

Hi Derrick,

Thanks for the quick response. Basically I want to compare the date passed from the client VB app which is in the format 'dd/mm/yyyy' with the date in database.

Adi

-------------------------
/me sux @sql server

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-23 : 11:32:36

will this do?

declare @Date varchar(10)
set @Date = '26.02.2004' -- this is passed as a parameter to sproc
SELECT ...
WHERE (convert(varchar(10), DateColumn, 103) = @Date) and ...

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources