Reputation: 23941
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
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