Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2003-10-23 : 17:13:51
|
| As noted in previous post, I'm trying to compute a subject's age by using difference betweeen date of birth and date of death. I need to break the result down into years, months, and days.Got a lot of help here on sqlteam and now I found a very detailed solution on the web, but can't figure how to implment this in QA, using the existing datetime fields in my table: demographics.dob and demographics.dodany help is appreciated.thx, the link...http://members.rogers.com/douglas.j.steele/Diff2Dates.html |
|
|
Granick
Starting Member
46 Posts |
Posted - 2003-10-24 : 12:29:17
|
| I think this function would at least get you the code you need. If you wanted to run this in-line, you could change it so that it returns either just one piece, so you would have 3 functions, one for each piece, or have it return a string with the the pieces.------CREATE FUNCTION [dbo].[fnc_DayMonthYear] ( @DOB datetime, @DOD Datetime) RETURNS @tVar table(Years int, Months int, Days int)ASBEGIN DECLARE @BeginYearVal int DECLARE @EndYearVal int DECLARE @NumMonths int DECLARE @BeginMonthVal int DECLARE @EndMonthVal int DECLARE @NumDays int DECLARE @BeginDayVal int DECLARE @EndDayVal int DECLARE @EndOfDOBMonth datetime DECLARE @EndOfDODMonth datetime DECLARE @EndOfDOBDays int -- Need to get the Date Portions so that we can build first of month values SET @BeginYearVal = Year(@DOB) SET @EndYearVal = Year(@DOD) SET @BeginMonthVal = Month(@DOB) SET @EndMonthVal = Month(@DOD) SET @BeginDayVal = Day(@DOB) SET @EndDayVal = Day(@DOD) -- First Get the LAST Day of the Birth Month SET @EndOfDOBMonth = CAST(@BeginMonthVal As varchar(2)) + '/1/' + CAST(@BeginYearVal As varchar(4)) SET @EndOfDOBMonth = DateAdd(dd, -1,DateAdd(mm, 1, @EndOfDOBMonth)) -- Now get the LAST day of the Death Month SET @EndOfDODMonth = CAST(@EndMonthVal As varchar(2)) + '/1/' + CAST(@EndYearVal As varchar(4)) SET @EndOfDODMonth = DateAdd(dd, -1,DateAdd(mm, 1, @EndOfDODMonth)) SET @EndOfDOBDays = Day(@EndOfDOBMonth) SET @NumMonths = DATEDIFF(mm, @EndOfDOBMonth, @EndOfDODMonth) IF(@BeginDayVal > @EndDayVal) SET @NumMonths = @NumMonths - 1 IF(@BeginDayVal > @EndDayVal) SET @NumDays = @EndOfDOBDays - @BeginDayVal + @EndDayVal ELSE IF(@BeginDayVal < @EndDayVal) SET @NumDays = @EndDayVal - @BeginDayVal ELSE SET @NumDays = 0 -- Fill our return var INSERT INTO @tVar SELECT @NumMonths / 12 As Years, (@NumMonths % 12) As Months, @NumDays As Days RETURNENDI haven't done a ton of testing, but for all the dates I have tested, it seems to calculate correctly.Hope it helps.ShannonShannon |
 |
|
|
|
|
|
|
|
found the DATEDIFF holy grail, but how to use it?