SQL 从字符串转换日期和/或时间时转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5124187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conversion failed when converting date and/or time from character string
提问by Saleh
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
I when I execute it I get this error :
当我执行它时，我收到此错误：
Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
the values for StatementDate, OtherDate are in this format "31/1/1994"as string before convert
StatementDate、OtherDate 的值"31/1/1994"在转换前采用这种格式作为字符串
What is the problem?
问题是什么？
EDIT:
编辑：
if I added this line of code :
如果我添加了这行代码：
CASE WHEN ISDATE(OtherDate) = 1 THEN
CONVERT(DATE, OtherDate, 103)
ELSE NULL END
I tried it and it's not working, I need some thing like it to check if vaild do the convert if not insert NULL value and I get the same error message
我试过了，但它不工作，我需要一些类似的东西来检查如果不插入 NULL 值，是否有效进行转换，并且我收到相同的错误消息
回答by RichardTheKiwi
ISDATE won't work since you cannot even specify a date format as you can with CONVERT. The following is a VERYelaborate test to reformat valid dates into YYYYMMDD for which ISDATE works predictably.
ISDATE 不起作用，因为您甚至无法像使用 CONVERT 那样指定日期格式。下面是一个非常复杂的测试，用于将有效日期重新格式化为 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
Comment out the last line WHERE ISDate(YYYYMMDDToTest) = 0to see what it does with each date.
注释掉最后一行WHERE ISDate(YYYYMMDDToTest) = 0以查看它对每个日期的作用。
EDIT
编辑
You can turn this into a function that replaces the ISDATE - but for the SPECIFICformat [d]d/[m]m/yyyy.
您可以将其转换为替换 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
回答by Cosmin
Try to use 103 for the second convert as well
尝试使用 103 进行第二次转换
回答by Martin Smith
This won't get all possible invalid dates (e.g. 30 February) but might help you find ones that need fixing.
这不会获得所有可能的无效日期（例如 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]'
