May 15, 2003 at 7:28 am
Hello.
I'm a newbie at UDF's. So, when I ran my select statement, I wasn't surprised when it returned an error...
My UDF is for getting the age of a patient.
CREATE FUNCTION dbo.f_get_age (@DOB datetime, @toDate datetime)
RETURNS int
AS
BEGIN
DECLARE @iReturn int
declare @datestr varchar(12)
declare @compdate datetime
select @datestr = cast(year(@todate) as char(4)) + '/' + cast(month(@DOB) as char(2)) + '/' + cast(day(@Dob) as char(2))
select @compdate = @datestr
SET @iReturn = datediff(yy,@DOB,@toDate)
if (datediff(dd,@toDate,@compdate)> 0)
begin
Set @iReturn = @iReturn - 1
end
return @iReturn
END
I would like to use it in my select statement to return the age of my patients.
Here's my select statement:
SELECT
A.entitycode,
B.Lastname,
B.dob as DOB,
dbo.f_get_age(B.dob, getdate())
FROM
medical.dbo.entity A
INNER JOIN v medical.dbo.patient B on A.entid = B.entityid
WHERE
A.termdate > getdate()
I get this error message returned:
Server: Msg 242, Level 16, State 3, Procedure f_get_age, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Any suggestions?
thanks
May 15, 2003 at 8:11 am
2 Things...
First, Is there really a need for DATETIME parameters? I think SMALLDATETIME should be sufficient for Birthdates, since I doubt many people keep track of when they were born to the millisecond... 🙂
Second, I think you might try CASTING your @datestr INTO a SMALLDATETIME before you set it equal to @compdate. Also, I'd try using '-' instead of '/', since you are appending the year first...that might work...
Jay
May 15, 2003 at 8:57 am
It works fine on my server. It might be a setting problem - how SQL Server formats dates?
Jeremy
May 15, 2003 at 10:12 am
Works fine for me too. I converted both to the format of yyyy/mm/dd and mm/dd/yyyy. Both worked fine. Seems like the format of the short-date on your machine.
You could also replace the whole issue and just compare DATEPARTS
DECLARE @iReturn int
SET @iReturn = datediff(yy,@DOB,@toDate)
if (datepart(month,@toDate) < datepart(month,@DOB) ) or
((datepart(month,@toDate) = datepart(month,@DOB) ) and (datepart(day,@toDate) < datepart(day,@DOB) ))
Set @iReturn = @iReturn - 1
I didn't test to see which performed better.
Guarddata-
May 15, 2003 at 11:48 am
Thanks for the replies. Here's what I've done:
Per JPIPES recommendations:
1.Changed datetime to smalldatetime - still recieve error.
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
2.Cast the @datestr before setting it. Not sure how to do this. The statement before does do a cast on the @datestr..?
3.Changed the '/' to '-'
Still recieve error above (#1).
Per Jeremy: SQL date formats - if you mean in the data the format is yyyy-mm-dd 00:00:00.
Per guarddata:
I copied and pasted your UDF formula and still get error message above.
Thanks for all your replies and helpful input. This is what I have so far:
ALTER FUNCTION dbo.f_get_age (@DOB smalldatetime, @toDate smalldatetime)
RETURNS int
AS
BEGIN
DECLARE @iReturn int
declare @datestr varchar(12)
declare @compdate datetime
select @datestr = cast(year(@todate) as char(4)) + '-' + cast(month(@DOB) as char(2)) + '-' + cast(day(@Dob) as char(2))
select @compdate = @datestr
SET @iReturn = datediff(yy,@DOB,@toDate)
if (datepart(month,@toDate) < datepart(month,@DOB) ) or
((datepart(month,@toDate) = datepart(month,@DOB) ) and (datepart(day,@toDate) < datepart(day,@DOB) ))
begin
Set @iReturn = @iReturn - 1
end
return @iReturn
END
May 15, 2003 at 11:51 am
Maybe try:
SELECT
A.entitycode,
B.Lastname,
B.dob as DOB,
dbo.f_get_age(CAST(B.dob AS SMALLDATETIME), getdate())
? Don't really know what it is...
May 15, 2003 at 12:04 pm
It actually does return data but gives me this error:
Server: Msg 242, Level 16, State 3, Procedure f_get_age, Line 16
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Very interesting....
May 15, 2003 at 12:07 pm
I have a sneaking suspicion that one of the dob data values is screwy. The column wouldn't happen to be NULLABLE would it?
May 16, 2003 at 6:55 am
Hmm. Thought that would be it also, but no rows returned are null. Since I am new at UDFs is there a way to test the UDF? Can I run it in a select statement without the other tables? I would like to test the UDF and make sure it works on my system. Then write a basic select statement with only the patient table and dob field.. Can you lead me in the right direction?
May 16, 2003 at 7:16 am
I think the first step would be to identify the offending line of code within the function, and run it directly in the SELECT statement against the same subset of data that is producing the error, and then, using more WHERE conditions, attempt to drill down to the offending data...that is, of course, if you get the same message as before...
So, try doing something like this against some subsets of data to find the offending data:
SELECT CAST(cast(year(GETDATE()) as char(4)) + '/' + cast(month(B.dob) as char(2)) + '/' + cast(day(B.dob) as char(2)) AS DATETIME)
FROM
medical.dbo.entity A
INNER JOIN v medical.dbo.patient B on A.entid = B.entityid
Let me know what you find...
May 16, 2003 at 7:35 am
Might be a good idea to handle leap years here ..
May 16, 2003 at 7:41 am
Note that the current month only has one digit in it.
When I run:
print cast(2003 as char(4)) + '/' + cast(5 as char(2)) + '/' + cast(16 as char(2))
I get as output:
2003/5 /16
which, I suspect, is an invalid date string as far as conversion goes.
Try casting to varchar.
RDF
R David Francis
May 16, 2003 at 7:47 am
quote:
Might be a good idea to handle leap years here ..
It's not obvious to me how this does not handle leap years. If your birthday is leap day, and it's not a leap year, then on February 28 it would not have been your birthday yet, and on March 1 it would have been. If you birthday is February 28, then on February 29 of a leap year your birthday's in the past, and if your birthday is March 1, then on leap day it hasn't passed yet.
What am I missing?
RD Francis
R David Francis
May 16, 2003 at 8:01 am
Sorry,
select @datestr = cast(year(@todate) as char(4)) + '-' + cast(month(@DOB) as char(2)) + '-' + cast(day(@Dob) as char(2))
DOB might be a leap day as you said
but todate is presumably current date = 2003 which is not -add these together you get
2003-02-29 - which is invalid
and yes - varchar would be a good idea
May 16, 2003 at 2:10 pm
I see where I went wrong - I was looking at the code supplied by guarddata, which fixes the leap year issue. That may still be the best solution....
RD Francis
R David Francis
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply