SQL错误“从字符串转换日期和/或时间时转换失败”，而不进行转换
SQL错误“从字符串转换日期和/或时间时转换失败”，而不进行转换
提问于 2022-12-02 10:57:03
使用以下查询(该查询不包含日期和/或时间转换的字符串)：
SELECT
Date,
RangeFam,
QTY,
Country
FROM (
SELECT Q_QTM as Qty,
Q_OFDFIN as Date,
Q_PAYS as Country,
CASE Q_DTRI05
WHEN 'eBALTIC - EBB' THEN 'EBAB'
WHEN 'Baltic -' THEN 'BALT'
WHEN 'eBALTIC - EBF' THEN 'EBAF'
WHEN 'Roof-tops - ENERGY' THEN 'ENER'
WHEN 'Roof-tops - FLEXAIR' THEN 'FLER'
END as RangeFam
FROM [sales].[dbo].[CARNET2_D]
WHERE Q_OFET='D'
and Q_TYPE='M'
UNION ALL
SELECT Qty,
Range_Code as RangeFam,
ShippingDate as Date,
CASE Country collate DATABASE_DEFAULT
WHEN 'France' THEN 'FRANCE'
WHEN 'Netherlands' THEN 'PAYS BAS'
WHEN 'Belgium' THEN 'BELGIQUE'
WHEN 'Portugal' THEN 'PORTUGAL'
WHEN 'Spain' THEN 'ESPAGNE'
END AS Country
FROM [crmv2].[dbo].[View_Forecastdata_Extended]
WHERE BaseUnit='1'
and (Project_status='Detection / Faisability' or Project_status='Execution' or Project_status='Quotation' or Project_status='Specification stage')
and [Report_S3A/B_group]='Rooftop'
and Sop='1'
and SopValid='1'
and (Sourcing='L&B' or Sourcing='LON' or Sourcing='BUR')
) as T1
ORDER BY Date我知道这个错误：
Msg 241，16级，状态1，第1行
从字符串转换日期和/或时间时，转换失败。
奇怪的是，如果我删除了所有与字段RangeFam相关的代码，那么查询就可以了，所以我想我错过了一些关于它的东西。
谢谢
当我说删除有关RangeFam的代码时，我的意思是以下查询没有问题：
SELECT
Date,
--RangeFam,
QTY,
Country
FROM (
SELECT Q_QTM as Qty,
Q_OFDFIN as Date,
Q_PAYS as Country
--CASE Q_DTRI05
-- WHEN 'eBALTIC - EBB' THEN 'EBAB'
-- WHEN 'Baltic -' THEN 'BALT'
-- WHEN 'eBALTIC - EBF' THEN 'EBAF'
-- WHEN 'Roof-tops - ENERGY' THEN 'ENER'
-- WHEN 'Roof-tops - FLEXAIR' THEN 'FLER'
--END as RangeFam
FROM [sales].[dbo].[CARNET2_D]
WHERE Q_OFET='D'
and Q_TYPE='M'
UNION ALL
SELECT Qty,
--Range_Code as RangeFam,
ShippingDate as Date,
CASE Country collate DATABASE_DEFAULT
WHEN 'France' THEN 'FRANCE'
WHEN 'Netherlands' THEN 'PAYS BAS'
WHEN 'Belgium' THEN 'BELGIQUE'
WHEN 'Portugal' THEN 'PORTUGAL'
WHEN 'Spain' THEN 'ESPAGNE'
END AS Country
FROM [crmv2].[dbo].[View_Forecastdata_Extended]
WHERE BaseUnit='1'
and (Project_status='Detection / Faisability' or Project_status='Execution' or Project_status='Quotation' or Project_status='Specification stage')
and [Report_S3A/B_group]='Rooftop'
and Sop='1'
and SopValid='1'
and (Sourcing='L&B' or Sourcing='LON' or Sourcing='BUR')
) as T1
ORDER BY Date回答 1
Stack Overflow用户
回答已采纳
发布于 2022-12-02 11:19:43
写查询的方式第一个子查询中的Q_OFDFIN内容是UNIONed，第二个子查询的内容是Range_Code。因为其中一个似乎是Date，而另一个似乎是字符串，所以服务器必须将它们转换为公共类型。
UNION和UNION都按位置处理结果字段，而不是子查询别名。
若要修复此问题，请确保列以正确的顺序显示：
FROM (
SELECT Q_QTM as Qty,
Q_OFDFIN as Date,
Q_PAYS as Country,
CASE Q_DTRI05
WHEN 'eBALTIC - EBB' THEN 'EBAB'
WHEN 'Baltic -' THEN 'BALT'
WHEN 'eBALTIC - EBF' THEN 'EBAF'
WHEN 'Roof-tops - ENERGY' THEN 'ENER'
WHEN 'Roof-tops - FLEXAIR' THEN 'FLER'
END as RangeFam
FROM [sales].[dbo].[CARNET2_D]
WHERE Q_OFET='D'
and Q_TYPE='M'
UNION ALL
SELECT Qty,
ShippingDate as Date,
CASE Country collate DATABASE_DEFAULT
WHEN 'France' THEN 'FRANCE'
WHEN 'Netherlands' THEN 'PAYS BAS'
WHEN 'Belgium' THEN 'BELGIQUE'
WHEN 'Portugal' THEN 'PORTUGAL'
WHEN 'Spain' THEN 'ESPAGNE'
END AS Country,
Range_Code as RangeFam
FROM [crmv2].[dbo].[View_Forecastdata_Extended]
...页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74654635
复制相关文章
相似问题
腾讯云开发者
