错误“将 varchar 值 'July' 转换为数据类型 int 时，消息 245，级别 16，状态 1，第 22 行转换失败." [英] Error "Msg 245, Level 16, State 1, Line 22 Conversion failed when converting the varchar value 'July' to data type int."

问题描述

SELECT  
    CAST((DATEDIFF(day, '21 JULY 2017', CAST(DAY(DATEADD(mm, DATEDIFF(mm, -1, '21 JULY 2017'), 0) -1) +
        LEFT(CONVERT(VARCHAR, DATENAME(MM,'21 JULY 2017'), 120), 10) + 
        CAST(YEAR('21 JULY 2017') AS VARCHAR(4)) AS DATETIME))) AS INT ) * (2.08 / DAY(EOMONTH('21 JULY 2017')))

我想计算天数.

但我收到一个错误:

将 varchar 值 'July' 转换为数据类型 int 时转换失败.

Conversion failed when converting the varchar value 'July' to data type int.

推荐答案

在不知道预期输出是什么，或者您尝试计算什么的情况下，我发现导致错误的原因是您尝试使用 + 符号将 int 值与字符串值连接起来.
在这些情况下，SQL Server 尝试将字符串值隐式转换为 int 值以执行添加操作(而不是您想要的连接操作).因此，我添加了对 varchar 和空格的强制转换，以便对 datetime 的强制转换有效.

Without knowing what the expected output is, or what is it that you try to calculate, I figured out that the cause to the error you get is that you try to concatenate int values with string values, using the + sign.
In these cases, SQL Server tries to implicitly convert the string values to int values to perform an adding operation (instead of the concatenation operation you want). Therefor, I've added casting to varchar as well as spaces so that the casting to datetime will work.

Select  cast((Datediff(day,'21 JULY 2017', CAST(cast(day(dateadd(mm,DateDiff(mm, -1, '21 JULY 2017'),0) -1) as varchar(10)) +' '+
        LEFT(CONVERT(VARCHAR(10), DATENAME(MM,'21 JULY 2017') , 120), 10) +' '+ 
        cast(year('21 JULY 2017') as varchar(4)) as datetime))) as int) * 
        (2.08/DAY(EOMONTH('21 JULY 2017')))

我得到的输出是0.6709677419350，但由于我不知道你的目标是什么，我不知道它是否正确.

The output I've got is 0.6709677419350, but since I have no idea what is your goal, I don't know if it's correct.

因为我有一些空闲时间，所以我能够重新编写您的查询并使用更简单的查询获得完全相同的结果:

Since I've had some spare time, I was able to re-write your query and get the exact same results with a much simpler query:

Select  Datediff(day,'21 JULY 2017', EOMONTH('21 JULY 2017')) * 
        (2.08/DAY(EOMONTH('21 JULY 2017'))) 

这篇关于错误“将 varchar 值 'July' 转换为数据类型 int 时，消息 245，级别 16，状态 1，第 22 行转换失败."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！