p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Syntax error converting datetime from character st (http://p2p.wrox.com/showthread.php?t=46759)

kkrish August 21st, 2006 10:47 AM

Syntax error converting datetime from character st
 
For the following proc:

CREATE PROCEDURE sample
@createdate datetime
AS

Declare @Str varchar(8000)
SET @Str = 'SELECT * from table_name
where CreationDate = "'+convert(datetime,@createdate,101)+'" AND
ID = Number'.
Exec(@Str)

I am getting the error

"Syntax error converting datetime from character string"

Please help.

Thanks.


Peso August 21st, 2006 12:49 PM

Why involve dynamic SQL?

CREATE PROCEDURE sample
@createdate datetime
AS

SET NOCOUNT ON

SELECT *
from table_name
where CreationDate = @createdate AND ID = Number


kkrish August 22nd, 2006 08:19 AM

Hi Peso,

I need to dynamically pass values to @createdate.

Please reply.

Thanks.


Gert August 22nd, 2006 11:10 AM

Hi.

As Peso says, you do not need dynamic SQL. Your in-parameter is of type DateTime and does not need to be converted. But, you need to make sure that if you use a string when calling the procedure, then the parameter must be in a format that can be converted to a datetime. If you use 'YYYYMMDD hh:mm' , you should be safe.

Gert


kkrish August 22nd, 2006 10:11 PM

I am passing a value in the format 'MM/DD/YYYY' from a data file, to @createdate using dynamic properties task.

If I use
CreationDate = "'+convert(varchar(20),@createdate,101)+'"

I am getting the syntax error.

Please advise.

Thanks.


Gert August 24th, 2006 05:15 AM

Hi.

Try to use the format YYYYMMDD instead.

Gert


Peso August 24th, 2006 05:48 AM

or use SET DATEFORMAT MDY in the stored procedure.

BUT YOU DON'T HAVE TO CONVERT THE DATE IN THE FIRST PLACE!


kkrish August 24th, 2006 03:16 PM

Hi,

Actually in the proc below

Declare @Str varchar(8000)
SET @Str = 'SELECT * from table_name
where CreationDate = "'+convert(varchar(20),@createdate,101)+'" AND
ID = Number'.
Exec(@Str)


the CreationDate is in the 'MM/DD/YYYY hh:mm:ss' format whereas the date value I am passing to @createdate is in 'MM/DD/YYYY' format


How do I remove/ignore the 'hh:mm:ss' part in the CreationDate?

Please help.

Thanks.


Peso August 25th, 2006 12:20 AM

Don't use dynamic SQL !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

SELECT *
from table_name
where CONVERT(VARCHAR, CreationDate, 112) = CONVERT(VARCHAR, @CreateDate, 112)
        AND ID = Number

Peso August 25th, 2006 12:46 AM

Or

SELECT *
from table_name
where CreationDate BETWEEN @CreateDate AND DATEADD(day, 1, @CreateDate)
        AND ID = Number




All times are GMT -4. The time now is 03:47 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.