SELECT CASE CAST 转换错误 [英] SELECT CASE CAST Conversion Error

问题描述

我正在查询应用程序的后端.无论出于何种原因，开发人员决定对所有字段使用 NVARCHAR.我遇到了日期"字段之一的问题——不知何故它填充了97630886".有一个伴随的时间"字段，对于我的查询，我试图将结果连接并转换为 DATETIME.我正在尝试使用 CASE 语句，但它出错了:char 数据类型到 datetime 数据类型的转换导致超出范围的 datetime 值.".显然所有条件都被评估了?有没有办法重写我的 SELECT 语句来处理这个问题?

I'm querying the back-end of an application. For what ever reason the developer decided to use NVARCHAR for all fields. I'm having a problem with one of the "date" fields--somehow it got populated with "97630886". There's a companion "time" field and for my query I'm trying to concatenate and CAST the results as a DATETIME. I'm trying to use a CASE statement but it's erroring: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". Apparently all conditions are evaluated? Is there a way I can rewrite my SELECT statement to handle this?

SELECT CASE 
WHEN LOG_DATE IS NULL AND LOG_TIME IS NULL THEN NULL 
WHEN LOG_DATE IS NULL THEN LOG_TIME 
WHEN LOG_TIME IS NULL THEN LOG_DATE 
WHEN ISDATE(LOG_DATE) = 0 OR ISDATE(LOG_TIME) = 0 THEN LOG_DATE + ' ' + LOG_TIME 
ELSE CAST(LOG_DATE + ' ' + LOG_TIME AS DATETIME)
END AS [LogDateTime] 
FROM ...

推荐答案

你不能在 CASE 表达式中混合数据类型(或者至少不注意它们会隐式转换为 OK)

You can't mix datatypes in CASE expressions (or at least without taking care that they will implicitly cast OK)

当遇到像下面这样的语句时，SQL Server 将使用数据类型优先级来确定表达式的整体数据类型应该是什么

when confronted with a stament like the following SQL Server will use datatype precedence to determine what the overall datatype of the expression should be

SELECT CASE WHEN 1=1 THEN 'not-a-date' ELSE getdate() END

对于上面的 datetime 具有比 char 更高的优先级，因此它隐式地将字符串转换为失败的日期.

For the above datetime has higher precedence than char so it implicitly casts the string to a date which fails.

以下成功，但是 sql_variant 具有更高的优先级

SELECT CASE WHEN 1=1 THEN cast('not-a-date' as sql_variant) ELSE getdate() END

因此您可以通过这种方式返回多种混合数据类型(我不确定 sql_variant 使用起来有多容易)

So you can return multiple mixed datatypes that way (I'm not sure how easy sql_variant is to work with though)

除此之外，对于无效的日期时间，您可以返回 NULL 而不是返回无意义的数据或将所有内容转换为字符串(如果必须返回).

Other than that you could return NULL for invalid datetimes instead of returning the nonsense data or cast everything to a string if you must return it.

这篇关于SELECT CASE CAST 转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！