t-sql在某种情况下可能使用其他数据类型?

问题描述

我有这个查询

SELECT
CASE WHEN dbo.CFE_PPHY.P77 IS NOT NULL OR dbo.CFE_PPHY.P77 <>''
       THEN MONTH(dbo.CFE_PPHY.P77)
     WHEN dbo.CFE_PPHY.P70 IS NOT NULL OR dbo.CFE_PPHY.P70 <>''
       THEN MONTH(dbo.CFE_SERVICE_EVTS.C10_2)
     ELSE COALESCE(CONVERT(VARCHAR,dbo.CFE_PPHY.P77)+
          CONVERT(VARCHAR,dbo.CFE_SERVICE_EVTS.C10_2),'toto') END
 AS CFELiasse_DateEffetEIRL_MM_N
FROM CFE_PPHY LEFT JOIN CFE_SERVICE_EVTS ON CFE_PPHY.colA = CFE_SERVICE_EVTS.colB

ELSE 部分让我头疼.

CFE_PPHY.P77 和 CFE_SERVICE_EVTS.C10_2 列具有日期时间格式.我正在把它们变成varchar.但是，当我运行查询时，出现以下错误

The columns CFE_PPHY.P77 and CFE_SERVICE_EVTS.C10_2 have date time format. I'm turning them into varchar. Yet when I'm running the query, I have the following error

很显然，我不能将 toto 转换为整数.很公平.但是，从我的角度来看，我已经将datetime格式转换为varchar格式，因此它应该可以完成工作.

Obviously, I cannot turn toto to an integer. Fair enough. However, from my point of view, I've converted the datetime format to a varchar format, so it should do the work.

我在哪里错了?

谢谢

推荐答案

您必须将所有的case表达式都转换为varchar.SQL决定将字段设置为int，因此"toto"无效.如果将所有表达式都转换为varchar，则应解决此错误.

You have to convert all of your case expressions to varchar. SQL is deciding to case the field as int so 'toto' is invalid. If all expressions are converted to varchar this error should be solved.

http://blog.sqlauthority.com/2010/10/08/sql-server-simple-explanation-of-data-type-precedence/

这篇关于t-sql在某种情况下可能使用其他数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！