如果您无法使用新的TRY_CONVERT
你可以使用这样的函数:
注意:这不会捕获像 31.06.2016 这样的错误日期,您必须修改BETWEEN 1 AND 31
如果你需要这个...
注意2:如果您的文本可能包含 xml 中禁止的字符,您应该替换<
with <
, >
with >
and &
with &
...
CREATE FUNCTION dbo.TestDate(@TestString VARCHAR(100))
RETURNS DATE
AS
BEGIN
DECLARE @x XML=CAST('<x>' + REPLACE(@TestString,'.','</x><x>') + '</x>' AS XML)
DECLARE @p1 VARCHAR(10) = @x.value('x[1]','varchar(10)');
DECLARE @p2 VARCHAR(10) = @x.value('x[2]','varchar(10)');
DECLARE @p3 VARCHAR(10) = @x.value('x[3]','varchar(10)');
IF LEN(@p1)=2 AND ISNUMERIC(@p1)=1 AND CAST(@p1 AS INT) BETWEEN 1 AND 31
AND LEN(@p2)=2 AND ISNUMERIC(@p2)=1 AND CAST(@p2 AS INT) BETWEEN 1 AND 12
AND LEN(@p3)=4 AND ISNUMERIC(@p3)=1 AND CAST(@p3 AS INT) BETWEEN 1900 AND 2100
RETURN CONVERT(DATETIME, @TestString , 103);
RETURN NULL;
END
GO
SELECT
dbo.TestDate('overdue') AS SureNoDate
,dbo.TestDate('01.04.2016') AS EuropeanDate
,dbo.TestDate('2016.04.01') AS WrongFormat
,dbo.TestDate('01.13.2016') AS BadDate;
GO
DROP FUNCTION dbo.TestDate;
结果
SureNoDate EuropeanDate WrongFormat BadDate
NULL 2016-04-01 NULL NULL
您可以传回一个有效日期(RETURN GETDATE()
?) 代替RETURN NULL
供你在外面比较。这取决于你的需求...