将varchar值'Jan'转换为数据类型int时转换失败。 [英] Conversion failed when converting the varchar value 'Jan ' to data type int.

问题描述

嗨大家好，



任何人都知道为什么这段代码会产生这个错误消息msg

消息245，等级16，状态1 ，第5行

将varchar值'Jan'转换为数据类型int时转换失败。

Hi Guys,

Anyone has an idea why this code is generating this error msg
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'Jan ' to data type int.

DECLARE @Period	DATETIME 
SET @Period = '2013/01/01'


SELECT     Division, DSCustName, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '1' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr1, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '2' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr2, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '3' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr3, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' AND 
                      [Quarter] = '4' THEN ISNULL(Amount, 0) ELSE 0 END AS Year1Qtr4, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year1' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS TotalYear1, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '1' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr1, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '2' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr2, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '3' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr3, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' AND [Quarter] = '4' THEN ISNULL(Amount, 0) 
                      ELSE 0 END AS Year2Qtr4, CASE WHEN dbo.YearNumber(@Period, [Year]) = 'Year2' THEN ISNULL(Amount, 0) ELSE 0 END AS TotalYear2, 
CASE WHEN ISNULL(Product, '') = '' THEN 'No Product Group Name' ELSE Product END as Name
FROM         TableName 
WHERE ([Year] = LEFT(@Period, 4) OR [Year] = CASE WHEN CAST(RIGHT(@Period, 2) as INT) <= 6 THEN LEFT(@Period, 4) - 1 ELSE LEFT(@Period, 4) + 1 END)

推荐答案

我认为@Period作为DATETIME的声明正在扭曲你认为你得到的数据。如果@ Period不是动态的并且总是'2013/01/01'那么我会把它变成VARCHAR（15）数据类型。如果它是动态日期，则尝试将该日期转换为在@Period变量设置中指定的varchar格式，然后再将其转换为日期左侧的右侧。如果需要澄清，请告诉我。

I believe the declaration of "@Period" as a DATETIME is skewing the data you think you are getting. If "@Period" is not going to be dynamic and will always be '2013/01/01' then I would make it a VARCHAR(15) data type. If it is a dynamic date, then try converting that date into the varchar format specified in your setting of "@Period" variable before casting it and getting the RIGHT of LEFT side of the date. Let me know if that needs clarification.

这篇关于将varchar值'Jan'转换为数据类型int时转换失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！