May 20, 2003 at 12:17 pm
Have been doing some testing.
Tried the select statement using only the udf and I provided the data:
SELECT DBO.F_GET_AGE(10 aug 1970, GETDATE())
**by the way, no that's not my REAL dob!!:D
The UDF functioned correctly and returned correct data.
Then I tried this:
SELECT DBO.F_GET_AGE(P.dob, GETDATE())
from
patient P
Same error message:
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.
Went through every record (3000+) to see if someone had entered a char value. Only thing I found was one row is NULL. So, I modified the select statement to:
SELECT DBO.F_GET_AGE(P.dob, GETDATE())
from
patient P
where P.dob is not null
Same error message as above. Any ideas?
Funny thing- I still get data and the correct ages are there, but get this error. hmphhhhhh
May 30, 2003 at 11:36 am
Used this function and it does not return errors.
CREATE FUNCTION dbo.f_get_age (@DOB datetime, @toDate datetime)
RETURNS int
AS BEGIN
DECLARE @iReturn int
DECLARE @iYears int
DECLARE @iMonths int
DECLARE @iDays int
SET @iYears = YEAR(@toDate) - YEAR(@DOB)
SET @iMonths = MONTH(@toDate) - MONTH(@DOB)
SET @iDays = DAY(@toDate) - DAY(@DOB)
IF @iMonths < 0 OR (@iMonths = 0 and @iDays < 0)
BEGIN
SET @iYears = @iYears-1
END
SET @iReturn = @iYears
RETURN @iReturn
END
Works for me!!
later
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply