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.

Why involve dynamic SQL?

CREATE PROCEDURE sample
@createdate datetime
AS

SET NOCOUNT ON

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

Hi Peso,

I need to dynamically pass values to @createdate.

Please reply.

Thanks.

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

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.

Hi.

Try to use the format YYYYMMDD instead.

Gert

or use SET DATEFORMAT MDY in the stored procedure.

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

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.

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

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

Or

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