CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlstored-procedures
burakim
burakim

Reputation: 482

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.

Upvotes: 1

Views: 2412

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You need to put quotes around the dates:

declare @sql nvarchar(max) = 'Select TimeStamp From WindData Where TimeStamp between '''+@startdate+''' and '''+@finishdate+'''')
EXEC(@sql);

Or, better yet, use sp_executesql:

declare @sql nvarchar(max) = 'Select TimeStamp From WindData Where TimeStamp between @startdate and @finishdate')
exec sp_executesql @sql, N'@startdate date, @finishdate date', @startdate = @startdate, @finishdate = @finishdate;

Upvotes: 1

valex
valex

Reputation: 24144

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;
...

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

CREATE PROC yourSPName
(@fetchtype int,
@startdate nvarchar(22),
@finishdate nvarchar(22)
)
AS
DECLARE @sqlstr varchar(2500)
if (@fetchtype = 0)
BEGIN

Select TimeStamp From WindData Where TimeStamp between convert(datetime,@startdate) and convert(datetime,@finishdate)

END

Upvotes: 0

Related Questions