burakim burakim - 3 months ago 20
SQL Question

Datetime syntax error

I'm newbie at SQL Server. I'm stuck on a problem that I can't solve. I want to write a stored procedure.

TimeStamp
column's datatype is
datetime
.

This my stored procedure:

@fetchtype int,
@startdate nvarchar(22),
@finishdate nvarchar(22)
AS
if (@fetchtype = 0)
BEGIN
PRINT('Select TimeStamp ' From WindData Where TimeStamp between '+@startdate+' and '+@finishdate)
EXEC('Select TimeStamp ' From WindData Where TimeStamp between '+@startdate+' and '+@finishdate)
END


An also my execution query is

DECLARE @return_value int

EXEC @return_value = [dbo].[Get_Values]
@columnnames = N'V81_Avg',
@fetchtype = 0,
@startdate = N'2013-04-23 12:58:40.000',
@finishdate = N'2013-04-23 12:59:00.000'

SELECT 'Return Value' = @return_value


But when I execute my query I get this error


Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '12'.


I think I didn't write properly datetime format.

Answer

The problem here is in SQL query string:

It should be:

PRINT('Select TimeStamp From WindData Where TimeStamp between '''+@startdate+''' and '''+@finishdate+'''');
EXEC('Select TimeStamp From WindData Where TimeStamp between '''+@startdate+''' and '''+@finishdate+'''') 

But the best way is to use these parameters as DATETIME and avoid dynamic query and replace EXEC() with just SELECT:

@startdate datetime,
@finishdate datetime

...
Select TimeStamp From WindData Where TimeStamp between @startdate and @finishdate;
...
Comments