CODEWITHSUNDEEP
visual-studiot-sql
bernie2436
bernie2436

Reputation: 23941

Confused by T-SQL: different output when I run a query in query window and as part of scalar function

Note: the patient data displayed below is "dummy" data that I made up. It is not the actual information for an actual patient.

I have a function with the following conversion in it:

Declare @bdate date 
set @bdate =  CONVERT ( date , left(@dob,8) , 112 )

If I just run this in a query window, it converts the date fine

select CONVERT(date, left('19900101', 8), 112)  //returns a good date

But if I step through a scalar function with the same code in it in visual studio I get an error...

Declare @bdate date 
set @bdate =  CONVERT ( date , left(@pidPatientDob,8) , 112 )

throws...

Running [dbo].[getAgeAtTestDate] ( @obxTestDate = '20120101', @pidPatientDob = '19900101' ).

Conversion failed when converting date and/or time from character string. Invalid attempt to read when no data is present.

Why does it work in the query window but not in the function? It seems like the parameters are getting filled properly in the function.

Here is the full text of the function, which is returning null (I think because of the error)

ALTER FUNCTION [dbo].[getAgeAtTestDate] 
(
    -- Add the parameters for the function here
    @obxTestDate as nvarchar(50), @pidPatientDob as nvarchar(50)
)
RETURNS int
AS
BEGIN
    Declare @bdate date 
    set @bdate =  CONVERT ( date , left(@pidPatientDob,8) , 112 )

    Declare @testDate date 
    set @testDate =  CONVERT ( date , left(@testDate,8) , 112 )

    -- Return the result of the function
    RETURN datediff(mm,  @testDate, @bdate)
END

Upvotes: 0

Views: 51

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181097

Your parameter is called obxTestDate, not testDate, so change;

set @testDate =  CONVERT ( date , left(@testDate,8) , 112 )

into

set @testDate =  CONVERT ( date , left(@obxTestDate,8) , 112 )

and things will work better.

As a side note, I think you reversed the DATEDIFF too, the start date should come before the end date;

RETURN datediff(mm,  @bdate, @testDate)

Upvotes: 2

Related Questions