sql中的日期时间转换错误。 [英] Datetime conversion error in sql.

问题描述

我有一个存储过程，例如：

I have a stored proc like :

create proc sp_trade
(
 @ID int,
 @TradeDate datetime
)

declare @query varchar(max)

set @query='select into table1
            where datediff(d,table1.colDate,@TradeDate)>0'

exec(@query)

抛出错误'转换失败时转换日期时间从字符串'在sql server 2005中。

我试过'+ CONVERT（VARCHAR（50），CAST（@TradeDate AS） DATETIME），101）+'但是，由于在varchar中更改了datediff，并且datediff需要datefield。

It throws error 'conversion failed when converting datetime from character string' in sql server 2005.
I tried '+ CONVERT(VARCHAR(50), CAST(@TradeDate AS DATETIME), 101)+' but then datediff doesn't work since it is changed in varchar and datediff expects datefield.

推荐答案

I得到完全不同的错误，但这里有一些解决问题的建议



首先 select into table1 在语法上是不正确的 - 你在选什么？你需要一个列列表，或者最坏的 *



其次 - 当你执行查询时不再声明 @TradeDate ，您将收到类似于

I get completely different errors but here are some suggestions for fixing your problem

Firstly select into table1 is not syntactically correct - what are you selecting? You need a column list in there, or at worst *

Secondly - by the time you execute the query @TradeDate is no longer declared and you will get an error similar to

引用的错误：

必须声明标量变量@TradeDate

Must declare the scalar variable "@TradeDate"

您将遇到生成动态sql的麻烦，因此您需要插入传递给该查询字符串的值

You are going to the trouble of generating dynamic sql so you need to insert the value passed into that query string

set @query='select into table1
            where datediff(d,table1.colDate,''' + @TradeDate + ''')>0'

或为什么要烦扰动态sql at all ...

or why bother with dynamic sql at all...

declare @query varchar(max)

select into table1
            where datediff(d,table1.colDate,@TradeDate) > 0
exec(@query)

首先看，替换：

On the first look, replace:

set @query='select into table1
            where datediff(d,table1.colDate,@TradeDate)>0'

with：

with:

set @query='select Field1, Field2, ..., FieldN
into table1 
where datediff(d,table1.colDate,' + @TradeDate + ')>0'

另一方面，为什么要将查询构建为变量类型字符串？只需exec语句：

On the other hand, why to build query as variable type string? Just exec statement as:

select Field1, Field2, ..., FieldN
into table1 
where datediff(d,table1.colDate, @TradeDate)>0

如需了解更多信息，请参阅：INTO条款 [ ^ ]

这篇关于sql中的日期时间转换错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！