SQL：将varchar转换为datetime时出错 [英] SQL: Error when converting varchar to datetime

问题描述

$
AS
（
SELECT'19'+ SUBSTRING（column，0，3）+' - '+ SUBSTRING （列，3，2）+' - '+ SUBSTRING（列，5，2）AS有效日期
FROM表
WHERE SUBSTRING（列，3，2）< ='12'月
AND SUBSTRING（column，3，2）> ='01'--Min月
AND ISNULL（SUBSTRING（column，3，2），''）<> - 空字符串
AND SUBSTRING（列，5，2）< ='31' - 日期
AND SUBSTRING（列，5，2）> ='01' - 月份
AND ISNULL（SUBSTRING（column，5，2），''）<>'' - 空字符串
AND LEN（'19'+ SUBSTRING（column，0，3）+' '+ SUBSTRING（列，3，2）+' - '+ SUBSTRING（列，5，2））= 10 - 匹配10个字符格式
）

SELECT CONVERT（DATETIME ，ValidDate）
FROM Valid_dates

当我在SQL Server Management Studio中执行此查询时，得到以下错误messag e：将varchar数据类型转换为datetime数据类型导致超出范围的值。

我已经过滤出所有无效日期。不存在的日期，如2月30日，4月31日，6月31日等，不会被过滤出来，你可以看到，但我检查了他们，他们不存在。只运行CTE查询产生大量的结果，我找不到任何差异。

解决方案

这是SQL Server的一个问题。它不能保证查询中的子句的顺序。没有任何意义，（即使在CTE中）

注意：这是优化器的功能。通过重新排列查询的不同组件的评估，优化器可以创建更好的查询计划。

在SQL Server 2012+中，最简单的方法是 TRY_CONVERT（）：

  with。 。 。 
从valid_dates选择try_convert（datetime，validdate）
; 
  

在早期版本中，您可以使用案例 ：

  select（当<所有你的条件这里> 
然后转换（datetime，validdate）
 end）
  

SQL Server 确保 case 正常情况下的语句（有一些古怪的情况与聚合），所以这也解决了这个问题。

WITH Valid_dates (ValidDate)
AS
(
    SELECT '19' + SUBSTRING(column, 0, 3) + '-' + SUBSTRING(column, 3, 2) + '-' + SUBSTRING(column, 5, 2) AS ValidDate
    FROM table
    WHERE SUBSTRING(column, 3, 2) <= '12' --Max month
    AND SUBSTRING(column, 3, 2) >= '01' --Min month
    AND ISNULL(SUBSTRING(column, 3, 2), '') <> '' --Empty string
    AND SUBSTRING(column, 5, 2) <= '31' --Max day
    AND SUBSTRING(column, 5, 2) >= '01' --Min month
    AND ISNULL(SUBSTRING(column, 5, 2), '') <> '' --Empty string
    AND LEN('19' + SUBSTRING(column, 0, 3) + '-' + SUBSTRING(column, 3, 2) + '-' + SUBSTRING(column, 5, 2)) = 10 --Must match 10 character format
)

SELECT CONVERT(DATETIME, ValidDate)
FROM Valid_dates

When I execute this query in SQL Server Management Studio, I get the following error message: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I have filtered out all invalid dates. Non-existent dates such as Feb 30, Apr 31, Jun 31 etc. are not filtered out per se as you can see, but I have checked for them, and they do not exist. Running only the CTE query yields plenty of results with which I can not find any discrepancies.

解决方案

This is an issue with SQL Server. It does not guarantee the order of evaluation of clauses in the query. There is no sense that the where (even in the CTE) is evaluated "before" the rest of the query.

Note: This is a feature of the optimizer. By rearranging the evaluation of different components of the query, the optimizer can create a better query plan.

In SQL Server 2012+, the simplest method is TRY_CONVERT():

with . . .
select try_convert(datetime, validdate)
from valid_dates;

In earlier versions, you can use case:

select (case when <all your conditions here>
             then convert(datetime, validdate)
        end)

SQL Server does guarantee the sequential evaluation of case statements under normal circumstances (there are some quirky situations with aggregations), so this also fixes the problem.

这篇关于SQL：将varchar转换为datetime时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！