由于无法像使用 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
dateformat
设置如何,select CONVERT(date, '31/1/1994', 103)
对我都有效。我猜你可能有一些无效的日期。 - Martin Smith