SQL Scalar Valued Function Return Null

Question

Problem:

I have a Scalar Valued Function returning data as VARCHAR(MAX) or NULL (function below), I am using this function to explode a long text string and grab single values (of multiple data types).

I am now trying to insert this data into another table but converted to proper data types but it's failing if the returned value is null.

The field I'm trying to populate is DATETIME NULL so if the function returns null I want to just select null, otherwise I want to convert the VARCHAR to a DATETIME, so far I have:

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

What I can't do is both handle the null value and convert to a DATETIME, can someone give me a line function to do this (if possible without calling the function twice)?

Error:

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 FUNCTION

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

Edit:

After the very useful help from AakashM, I have located the line and value causing the error, it was trying to pass dd-mm-yyyy as mm-dd-yyyy, working until a value for the day was over 12.

To resolve it I just changed:

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

To:

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

"it's failing if the returned value is null" - what's the error message? — AakashM
– AakashM, Commented Apr 3, 2012 at 9:41

AakashM · Accepted Answer · 2012-04-03 09:48:50Z

5

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

This isn't complaining about not being able to convert NULL from varchar to datetime. This is complaining about converting and ending up with a date outside the range of datetime. Proof:

A sample function that returns either a date-like string, or 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

Some values to pass to this function:

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

With the commented line as-is, we get

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

showing that NULL is fine as a return value from the function. However, uncomment the 2 line and we get

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

because the year 1750 is before the representable range of datetime (which is 1753 to 9999).

answered Apr 3, 2012 at 9:48

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bendataclear Over a year ago

This is definitely the problem, well spotted! The next problem I have is finding the offending value. I have checked distinct values for dates and time, dates are all in 2012 and times are between 00:00:00 and 02:00:00. Is there a problem in Converting 00:00:00 => DATETIME?

AakashM Over a year ago

If you have access to the live data, I don't know of a better way than writing some diagnostic code that cursors through the data, attempting the conversion on each row in turn...

bendataclear Over a year ago

I have access so I'll run through this now (*googles TSQL CURSOR) :)

bendataclear Over a year ago

I have checked the data, I will update the question with details.

Collectives™ on Stack Overflow

SQL Scalar Valued Function Return Null

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related