日期和/或时间字符串转换失败

Question

I have this query :

set IDENTITY_INSERT dbo.OtherData1 ON


INSERT INTO OtherData1 (OtherDataID, EmployeeID, OtherDate, OType, OSubject, StatementNo, StatementDate, Info, OtherAs, FolderSerial)

    SELECT OtherDataID, EmployeeID, 
                 CONVERT(DATE, OtherDate, 103), 
                     OType, OSubject, StatementNo, 
                     CONVERT(DATE, StatementDate), Info,
                     CASE OtherAs
           WHEN  'f' THEN 1
              WHEN  's' THEN 2
              WHEN 't' THEN 3
              WHEN 'f' THEN 4
              WHEN 'p' THEN 5
              WHEN 'o' THEN 6
           ELSE NULL END
           , FolderSerial
    FROM OtherData

当我执行时，出现以下错误：

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.

StatementDate和OtherDate的值是以字符串形式表示，格式为"31/1/1994"，在转换前需要注意。

问题是什么？

编辑：

如果我添加了以下代码：

CASE WHEN ISDATE(OtherDate) = 1 THEN 
  CONVERT(DATE, OtherDate, 103)
ELSE NULL END

我试过了，它不起作用，我需要类似的东西来检查是否有效，如果无效则插入空值，并获得相同的错误消息。

- Saleh

---

为什么要将日期存储为字符串，而且还是不支持的格式？ - OMG Ponies

@StackOverflowException：日期字符串格式："31/1/1994" - Saleh

无论dateformat设置如何，select CONVERT(date, '31/1/1994', 103)对我都有效。我猜你可能有一些无效的日期。 - Martin Smith

你需要将日期时间转换为字符串吗？ - Bala R

@Martin：我的表格中有超过180,000行，有没有什么方法可以避免无效日期？ - Saleh

Answer 1

由于无法像使用 CONVERT 一样指定日期格式，因此 ISDATE 不起作用。以下是一个非常详细的测试，用于将有效的日期重新格式化为 YYYYMMDD，这样 ISDATE 就可以可靠地工作。

with otherdata(StatementDate) as (
select convert(varchar(10),'1/10/2011') union all
select '28/2/2911' union all
select '8/12/2011' union all
select '13/13/2011' union all
select '13/12/2011' union all
select '12/13/2011' union all
select '2/29/2011' union all
select '29/2/2011' union all
select '29/2/2012' union all
select '2011-02-01' union all
select '1/1/11' union all
select '1/1/99' union all
select '' union all
select null)

-- THE QUERY YOU NEED is below this line. The above virtually sets up a table
-- without having to physically create it
select
    statementdate,
    YYYYMMDDToTest,
    ISDate(YYYYMMDDToTest)
from otherdata
cross apply
       (
       select TheYear = case
       when not statementdate like '%[0-9]/%[0-9]/%[0-9][0-9]' then null
       when convert(int,replace(statementdate,'/','')) != replace(statementdate,'/','') then null
       when statementdate like '%[0-9]/%[0-9]/[0-9][0-9]' then
           case when RIGHT(statementdate,2) >=50
           then '19'+RIGHT(statementdate,2)
           else '20'+RIGHT(statementdate,2)
           end
       when statementdate like '%[0-9]/%[0-9]/[1-9][0-9][0-9][0-9]' then
           RIGHT(statementdate,4)
       end
       ) A
cross apply
       (
       select YYYYMMDDToTest = case
       when TheYear is not null then
           TheYear
           + -- month
           right(100+SUBSTRING(statementdate, charindex('/',statementdate) +1,
           charindex('/',statementdate,charindex('/',statementdate)+1)-
           charindex('/',statementdate)-1),2)
           + -- day
           right(100+LEFT(statementdate, charindex('/', StatementDate) -1),2)
       end
       ) B
WHERE ISDate(YYYYMMDDToTest) = 0

将最后一行WHERE ISDate(YYYYMMDDToTest) = 0注释掉，以查看它对每个日期的处理方式。

---

编辑

您可以将此转换为一个函数，以替换ISDATE函数，但仅针对[d]d/[m]m/yyyy 特定格式。

create function dbo.superIs103Date(@any varchar(50))
returns bit as begin
declare @theyear varchar(10)
set @TheYear = case
       when not @any like '%[0-9]/%[0-9]/%[0-9][0-9]' then null
       when convert(int,replace(@any,'/','')) != replace(@any,'/','') then null
       when @any like '%[0-9]/%[0-9]/[0-9][0-9]' then
           case when RIGHT(@any,2) >=50
           then '19'+RIGHT(@any,2)
           else '20'+RIGHT(@any,2)
           end
       when @any like '%[0-9]/%[0-9]/[1-9][0-9][0-9][0-9]' then
           RIGHT(@any,4)
       end
declare @YYYYMMDDToTest varchar(50)
set @YYYYMMDDToTest = case
       when @TheYear is not null then
           @TheYear
           + -- month
           right(100+SUBSTRING(@any, charindex('/',@any) +1,
           charindex('/',@any,charindex('/',@any)+1)-
           charindex('/',@any)-1),2)
           + -- day
           right(100+LEFT(@any, charindex('/', @any) -1),2)
       end
return ISDate(@YYYYMMDDToTest)
end
GO

-- example usage
select dbo.superIs103Date('33/1/1700')
-- returns 0

- RichardTheKiwi

---

你能否给我解释一下，我如何应用这个查询，因为我试过了，但不知道该如何将它与我的查询合并。 - Saleh

Answer 2

这个方法不能找到所有可能的非法日期（例如2月30日），但可以帮助你找到需要修复的日期。

SELECT StatementDate 
 FROM OtherData
 WHERE StatementDate NOT LIKE '[0-3][0-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9]'
  AND  StatementDate NOT LIKE '[1-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9]'
  AND  StatementDate NOT LIKE '[0-3][0-9]/[1-9]/[1-2][0-9][0-9][0-9]'
  AND  StatementDate NOT LIKE '[1-9]/[1-9]/[1-2][0-9][0-9][0-9]'

- Martin Smith

---

@Szamdev - 我添加了一个答案来涵盖这个答案没有覆盖到的部分。 - RichardTheKiwi

Answer 3

尝试在第二次转换中也使用103

- Cosmin