SQL标量值函数返回Null

Question

问题：

我有一个标量值函数，它以VARCHAR（MAX）或NULL（下面的函数）的形式返回数据，我使用这个函数来分解一个长文本字符串并获取（多种数据类型的）单个值。

我现在正试图将此数据插入另一个表中，但已转换为正确的数据类型，但如果返回值为null，则会失败。

我试图填充的字段是DATETIME NULL，所以如果函数返回null，我只想选择null，否则我想将VARCHAR转换为DATETIME，到目前为止我有：

(SELECT CONVERT(DATETIME, dbo.UDEF_GetFromTextString('Date=', ',', RawData))) AS LineDate,

我不能同时处理null值和转换为DATETIME，有人能给我一个行函数来做这件事吗（如果可能的话，不需要调用两次函数）？

错误：

Msg 242, Level 16, State 3, Procedure UDEF_DC_TRANSLATE_CALL_DATA, Line 11
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

UDEF_GetFromTextString函数

CREATE FUNCTION [dbo].[UDEF_GetFromTextString]
-- Input start and end and return value.
   (@uniqueprefix VARCHAR(100),
    @commonsuffix VARCHAR(100),
    @datastring VARCHAR(MAX) )
RETURNS VARCHAR(MAX) -- Picked Value.
AS
BEGIN
    DECLARE @ADJLEN INT = LEN(@uniqueprefix)
    SET @datastring = @datastring + @commonsuffix

   RETURN ( 
    CASE WHEN (CHARINDEX(@uniqueprefix,@datastring) > 0) 
         AND (CHARINDEX(@uniqueprefix + @commonsuffix,@datastring) = 0)
    THEN SUBSTRING(@datastring, PATINDEX('%' + @uniqueprefix + '%',@datastring)+@ADJLEN, CHARINDEX(@commonsuffix,@datastring,PATINDEX('%' + @uniqueprefix + '%',@datastring))- PATINDEX('%' + @uniqueprefix + '%',@datastring)-@ADJLEN) ELSE NULL END
    )
END

编辑：

在AakashM提供了非常有用的帮助后，我找到了导致错误的行和值，它试图将dd-mm-yyyy传递为mm-dd-yyyy，直到当天的值超过12。

为了解决这个问题，我刚刚改变了：

(SELECT CONVERT(DATETIME, UDEF_GetFromTextString('Date=', ',', RawData))) AS CallDate

收件人：

(SELECT CONVERT(DATETIME, UDEF_GetFromTextString('Date=', ',', RawData), 105)) AS CallDate

Answer 1

将varchar数据类型转换为datetime数据类型导致值超出范围。

这并不是抱怨无法将NULL从varchar转换为datetime。这是在抱怨转换后的日期超出了datetime的范围。证明：

返回类似日期的字符串或NULL:的示例函数

create function Fexample (@i int) RETURNS varchar(max) 
as
begin
return case 
when @i = 5 then '2012-05-16' 
when @i = 2 then '1750-01-01'
else null end
end
go

要传递到此函数的一些值：

declare @a table ( ii int, s datetime null )
--insert @a values ( 2, null)
insert @a values ( 3, null)
insert @a values ( 5, null)
update @a
set s =convert(datetime, dbo.Fexample(ii))
select * from @a

有了评论行，我们得到了

ii          s
----------- -----------------------
3           NULL
5           2012-05-16 00:00:00.000

表明CCD_ 10作为函数的返回值是好的。然而，取消对2行的注释，我们得到

结果是varchar数据类型转换为datetime数据类型值超出范围。

因为年份1750在CCD_ 12的可表示范围（其为1753到9999）之前。