从字符串转换日期和/或时间时，如何解决此错误转换失败。 [英] How Do I Resolve This Error Conversion Failed When Converting Date And/Or Time From Character String.

问题描述

如何在两个日期之间选择数据

Hi ,
How do i select the Data's between the two date's

declare @fromdate Datetime,@todate datetime,@sql nvarchar(max) ;
select @fromdate ='2013/05/01'
select @todate='2014/05/31'
select @adv_id='4'
set @sql= N'';
set @sql += N' select *from table where paid_date between '+@fromdate+' and '+@todate+''
print @sql
EXEC sp_executesql @sql;

但它说

but it says

Conversion failed when converting date and/or time from character string.

推荐答案

set @sql += N' select *from table where paid_date between '+@fromdate+' and '+@todate+''

此行强制SQL在类型之间进行转换（它包含不同的类型：字符串和日期时间）。由于你没有指定如何做到这一点，转换SQL是通过行的最后一个参数，这是datetime的类型！您已明确将datetime变量转换为字符串...

This line forces SQL to convert between types (it contains different types: string and datetime). As you didn't specified how to do that convert SQL goes by the last parameter on the line, which is type of datetime! You have explicitly convert datetime variables to string...

set @sql += N' select *from table where paid_date between '''+convert(nvarchar, @fromdate, 103)+''' and '''+convert(nvarchar, @todate, 103)+''''

另请注意引号！它确保CONVERT创建的日期字符串将被视为字符串（用引号括起）

Also notice the quotes! It ensures that the date-strings created by CONVERT will be treated as string (enclosed by quotes)

这篇关于从字符串转换日期和/或时间时，如何解决此错误转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！