日期时间语法错误

Question

我是SQL Server的新手。我陷入了一个无法解决的问题。我想写一个存储过程。

TimeStamp列的数据类型为datetime。

这是我的存储过程：

    @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

我的一个执行查询也是

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

但当我执行查询时，我得到了这个错误

消息102，级别15，状态1，第1行
"12"附近的语法不正确。

我想我写的日期时间格式不正确。

Answer 1

这里的问题出现在SQL查询字符串中：

应该是：

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

但最好的方法是将这些参数用作DATETIME，避免动态查询，并将EXEC()替换为SELECT：

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

Answer 2

您需要在日期周围加引号：

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

或者，更好的是，使用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;

Answer 3

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