use datediff to deliver age by- yr,/mo/ wk/day

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

use datediff to deliver age by- yr,/mo/ wk/day

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-21 : 15:22:10

Hi, I'm using:

DateDIFF(yy, DEMOGRAPHICS.DOB,
DEMOGRAPHICS.DOD) - CASE WHEN DEMOGRAPHICS.DOD >= DateAdd(yy,
DateDIFF(yy, DEMOGRAPHICS.DOB, DEMOGRAPHICS.DOD),
DEMOGRAPHICS.DOB)
THEN 0 ELSE 1 END

to get a rounded Age @ Death, but I wondered, would it be possible to use datediff to deliver a detailed version of this variable, i.e. if

dob = 11/2/1978
dod = 11/20/1978

instead of age coming out as 0 with the above calc, it would come out as

0 yrs
0 mos
2 wks
4 days

My thinking is that I'd run datediff against the DOD-DOB four times, testing for yrs, mos, wks, days, and then send results to a "detailed" age table only in cases where yrs = 0 (this is the requirement here).

I'm stuck at the point where I test DOD-DOB. How do I test for a full year/month/week/day?

thx.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 16:30:56

This appears to work:


DECLARE @date1 DATETIME
DECLARE @date2 DATETIME

SET @date1 = 'Oct 2 1978'
SET @date2 = 'Nov 4 1978'

SELECT 
	CONVERT(VARCHAR(50), DATEDIFF(year, @date1, @date2)) + ' years, ' + 
	CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(year, @date1, @date2), @date1), @date2)) + ' months, ' + 
	CONVERT(VARCHAR(50), DATEDIFF(week, DATEADD(month, DATEDIFF(month, DATEADD(year, DATEDIFF(year, @date1, @date2), @date1), @date2), @date1), @date2)) + ' weeks, ' + 
	CONVERT(VARCHAR(50), DATEDIFF(day, DATEADD(month, DATEDIFF(month, DATEADD(year, DATEDIFF(year, @date1, @date2), @date1), @date2), @date1), @date2)) + ' days.'
GO

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 16:46:02

My solution falls apart at weeks. I'll keep working on it.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-21 : 18:51:47

I have tested this with various date and I think it works for everything. I did not include weeks though.


DECLARE @date1 DATETIME
DECLARE @date2 DATETIME

SET @date1 = 'Oct 1 1978'
SET @date2 = 'March 16 1979'

SELECT 
	CONVERT(VARCHAR(50), DATEDIFF(day, @date1, @date2)/365) + ' years, ' + 
	CASE 
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2) - 1) + ' months, '
		ELSE CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2)) + ' months, '
	END +
	CASE
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEPART(day, @date2)) + ' days'
		ELSE CONVERT(VARCHAR(50), DATEPART(day, @date2 - 1)) + ' days'
	END AS HowOld

Let me know if it doesn't work and which dates didn't work.

Tara

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-22 : 10:41:54

Tara,

Hey, thanks for taking the time on this. Looks like it's working fine except DAYS are not resetting on the actual birthdate, i.e.

for

Oct 30 1978
Oct 29 1979

it is delivering the proper result: 0 years, 11 months, 29 days

however, on the birthdate of:

Oct 30 1978
Oct 30 1979

days are still hanging around: 1 years, 0 months, 29 days

instead of "resetting" to zero, at which point the DAYS counter would begin again.

thanks again for helping!

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-22 : 10:55:28

I got it to reset days to zero, and then begin incrementing days again, but now it is not adding days properly, i.e. when a new month begins, it is resetting day counter at 1. If you run the code below, you'll see what I mean. This should yield 2 days. Getting closer!

thx

=============================================================

DECLARE @date1 DATETIME
DECLARE @date2 DATETIME

SET @date1 = '10/30/1978'
SET @date2 = '11/01/1979'

SELECT
CONVERT(VARCHAR(50), DATEDIFF(day, @date1, @date2)/365) + ' years, ' +
CASE
WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2) - 1) + ' months, '
ELSE CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2)) + ' months, '
END +
CASE
WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEPART(day, @date2)) + ' days'
ELSE CONVERT(VARCHAR(50), DATEDIFF(day, DATEADD(month, DATEDIFF(month, @date1, @date2), @date1), @date2)) + ' days '
END AS HowOld

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-22 : 11:11:40

check that...the above is delivering negative months so I'm off base...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-22 : 12:35:51

The only part that isn't working now (I think at least) is the ELSE statement when days is calculated. I'll work on that today as I find time. The dates that I set the variables to will show the problem.


DECLARE @date1 DATETIME
DECLARE @date2 DATETIME

SET @date1 = 'Oct 30 1978'
SET @date2 = 'Oct 31 1979'

SELECT 
	CONVERT(VARCHAR(50), DATEDIFF(day, @date1, @date2)/365) + ' years, ' + 
	CASE 
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2) - 1) + ' months, '
		ELSE CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2)) + ' months, '
	END +
	CASE
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEPART(day, @date2)) + ' days'
		WHEN DATEPART(day, @date1) = DATEPART(day, @date2)  AND DATEPART(month, @date1) = DATEPART(month, @date2) THEN CONVERT(VARCHAR(50), 0) + ' days'
		ELSE CONVERT(VARCHAR(50), DATEPART(day, @date2 - 1)) + ' days'
	END AS HowOld

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-22 : 12:38:51

I think I got it.

Try this one out with various date combinations. Let me know if it needs some tweaking.


SELECT 
	CONVERT(VARCHAR(50), DATEDIFF(day, @date1, @date2)/365) + ' years, ' + 
	CASE 
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2) - 1) + ' months, '
		ELSE CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2)) + ' months, '
	END +
	CASE
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEPART(day, @date2)) + ' days'
		WHEN DATEPART(day, @date1) = DATEPART(day, @date2)  AND DATEPART(month, @date1) = DATEPART(month, @date2) THEN CONVERT(VARCHAR(50), 0) + ' days'
		ELSE CONVERT(VARCHAR(50), DATEPART(day, @date2) - DATEPART(day, @date1)) + ' days'
	END AS HowOld

Is leaving out weeks ok for your project? It made it too difficult to code. I'm sure it is possible, but in my mind, weeks isn't needed for this type of information.

Tara

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-22 : 15:04:28

nope, don't need weeks, thx. weeks were the reason I was stumped in the first place. your latest was working fine until I began plugging in dates at the top of a specific month, i.e.

oct 4
oct 3

versus

oct 31
oct 30

the routine seems to be counting only days in the actual month for date2, rather than total days, so the oct 4,3 example returns:

0 years, 11 months, 3 days

while the oct 31,30 example returns:

0 years, 11 months, 30 days

total days should be 364 for each example, I think. thanks again for helping!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-22 : 17:57:54

Ok, I've got it to work EXCEPT for DOB that land on the day at the end of the month, such as Oct. 31st. The reason is that I need to build the date so that I can use DATEDIFF. Here's an example:

If the DOB were Jan. 26th and the DOD is October 15th. I need to get September 26th (which is DOD month - 1 for the month, DOB day, and DOD year) then do a DATEDIFF on October 15 and Sept. 26th. Well this doesn't work with Oct. 31st being the DOB, because the date that I build would be September 31, which isn't a valid date.

I know how to fix it, I just need to find a little bit of time to write it.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-22 : 18:53:45

Try this:



DECLARE @date1 DATETIME
DECLARE @date2 DATETIME

SET @date1 = 'Jan 13 1978'
SET @date2 = 'Oct 14 1979'

SELECT 
	CONVERT(VARCHAR(50), DATEDIFF(day, @date1, @date2)/365) + ' years, ' + 
	CASE 
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2) - 1) + ' months, '
		ELSE CONVERT(VARCHAR(50), DATEDIFF(month, DATEADD(year, DATEDIFF(day, @date1, @date2)/365, @date1), @date2)) + ' months, '
	END +
	CASE
		-- When DOB is Dec 31 and day part of DOB is greater than day part of DOD
		WHEN DATEADD(year, 1, (CONVERT(DATETIME, CONVERT(VARCHAR(50), DATEPART(month, DATEADD(month, 1, @date1))) + '-01-' + CONVERT(VARCHAR(50), DATEPART(year, @date1))) - 1)) = @date1 AND DATEPART(month, @date1) = 12 THEN CONVERT(VARCHAR(50), DATEDIFF(day, CONVERT(DATETIME, CONVERT(VARCHAR(50), DATEPART(month, @date2)) + '-01-' + CONVERT(VARCHAR(50), DATEPART(year, @date2))) - 1, @date2)) + ' days'
		-- When DOB Jan 31 and day part of DOB is greater than day part of DOD
		WHEN (CONVERT(DATETIME, CONVERT(VARCHAR(50), DATEPART(month, DATEADD(month, 1, @date1))) + '-01-' + CONVERT(VARCHAR(50), DATEPART(year, @date1))) - 1) = @date1 AND DATEPART(month, @date1) = 1 THEN CONVERT(VARCHAR(50), DATEDIFF(day, CONVERT(DATETIME, CONVERT(VARCHAR(50), DATEPART(month, @date2)) + '-01-' + CONVERT(VARCHAR(50), DATEPART(year, @date2))) - 1, @date2)) + ' days'
		-- When the day part of DOB is the last day of that month (Jan and Dec already handled) and day part is greater than day part of DOD
		WHEN (CONVERT(DATETIME, CONVERT(VARCHAR(50), DATEPART(month, DATEADD(month, 1, @date2))) + '-01-' + CONVERT(VARCHAR(50), DATEPART(year, @date1))) - 1) = @date1 THEN CONVERT(VARCHAR(50), DATEDIFF(day, CONVERT(DATETIME, CONVERT(VARCHAR(50), DATEPART(month, @date2)) + '-01-' + CONVERT(VARCHAR(50), DATEPART(year, @date2))) - 1, @date2)) + ' days'
		-- When the day part of DOB is greater than day part of DOD (some cases already handled above)
		WHEN DATEPART(day, @date1) > DATEPART(day, @date2) THEN CONVERT(VARCHAR(50), DATEDIFF(day, CONVERT(VARCHAR(50), DATEPART(month, DATEADD(month, -1, @date2))) + '-' + CONVERT(VARCHAR(50), DATEPART(day, @date1)) + '-' + CONVERT(VARCHAR(50), DATEPART(year, @date2)), @date2)) + ' days'
		--When the day part of DOB equals the day part of DOD
		WHEN DATEPART(day, @date1) = DATEPART(day, @date2)  AND DATEPART(month, @date1) = DATEPART(month, @date2) THEN CONVERT(VARCHAR(50), 0) + ' days'
		-- When the day part of DOB is less than the day part of DOD
		ELSE CONVERT(VARCHAR(50), DATEPART(day, @date2) - DATEPART(day, @date1)) + ' days'
	END AS HowOld

Try to break it. Let me know how it goes.

Tara

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-23 : 10:18:34

thanks so much for all the effort. If I plug in the following dates, the day count seems to be off:

SET @date1 = 'sep 29 1978'
SET @date2 = 'sep 28 1979'

yields: 0 years, 11 months, 30 days

when it should (I think) yield: 0 years, 11 months, 29 days

I see now that in order to determine accurate days, we need to count days left over following @date1. In this case it's 1 day left over in the month of Sept after Sept 29. Next we would add this remainder to the total days in @date2 to get accurate days: 1 + 28 = 29.

Similar problem with this example:

SET @date1 = 'oct 30 1978'
SET @date2 = 'oct 29 1979'

the routine yields: 0 years, 11 months, 29 days

based on my theory above, the proper day total would be @date1 leftover days in Oct (1) + @date2 days (29) = 30. In this case the routine has delivered one less day that the accurate day total, whereas in the sept example it delivered one more day than the accurate day total.

I may be oversimplifying, but could all your custom CASE stmts that cover @date1 days > @date2 days be replaced with one stmt that would somehow calculate days the way I've clunkily described above?

I wish I was more skilled with date manipulation. My test routines look a lot different than yours! Thanks again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 12:25:31

Yeah, my calculations might be a little off, but I think that I got you most of the way with the code.

It is totally possible that the > lines could be replaced by one statement, but I think that the logic is different for certain cases, which is why I commented the lines this time.

There are probably still some logic problems with the > lines, so a bunch of testing would need to be done to see if more > lines need to be added. I had to work a lot of the logic (how the calculation would be done without code) out on paper before I started coding it. If you do that, then I think you'll be able to understand what I am doing.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-24 : 07:54:45

quote:
.... based on my theory above ....

Sorry but I think that your approach is wrong and Tara's code is OK.
Look here, suppose someone was born on Aug 9 and died on Dec 3.
In this case Month-based Cycle completes on Nov 9, not on Aug 9.
More precisely, on Nov 8 23:59:59.99999999. So, what are the left over days?

They are:

of Nov (9, 10, 11, 12, 13, ..., 30) + of Dec (1, 2).

In your approach you'll count up here one more day - Aug 31. Why?

Below is my code for the subject (I can correct it if you'll be sticking to
your counting scheme):

declare @d1 datetime, @d2 datetime
set @d1='Oct 29 1978' set @d2='Dec 29 1979'

select

(datediff(mm,@d1,@d2)+sign(day(@d2)-day(@d1)))/12 YearCount,

(datediff(mm,@d1,@d2)+case when day(@d2)<day(@d1) then -1
else 0 end)%12 MonthCount,

case when day(@d2)>=day(@d1) then day(@d2)-day(@d1) else
datediff(dd,dateadd(mm,-1,@d2),@d2)-day(@d1)+day(@d2)
end DayCount

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-24 : 09:40:10

Stoad,

to answer your question as to why I counted the extra day, my logic was faulty, it appears. thanks for the clarification, and the code example.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-10-24 : 13:10:15

thanks to tara and stoad for the help with this. just fyi, I've used a slightly altered version of this code in a view to calculate infant age and kick this data back to a web page ONLY in cases where age < 1 yr. this has saved my users a lot of potentially inaccurate hand data entry. thx again.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-24 : 13:10:38

Oops... need to be corrected:

declare @d1 datetime, @d2 datetime
set @d1='Oct 29 1978' set @d2='Dec 29 1979'

select

(datediff(mm,@d1,@d2)+case when day(@d2)<day(@d1) then -1
else 0 end)/12 YearCount,

(datediff(mm,@d1,@d2)+case when day(@d2)<day(@d1) then -1
else 0 end)%12 MonthCount,

case when day(@d2)>=day(@d1) then day(@d2)-day(@d1) else
datediff(dd,dateadd(mm,-1,@d2),@d2)-day(@d1)+day(@d2)
end DayCount

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-25 : 20:02:15

Here two codes for the both schemes of counting of over-left days.
Sample dates: Aug 28 1978 and Dec 02 1978.

The 1st scheme counts extra-days like this:
Aug28, Aug29, Aug30, Aug31 {Sep}{Oct}{Nov} Dec01
Total: 5 days.

The 2nd scheme counts extra-days like this:
{Aug28-Sep27}{Sep28-Oct27}{Oct28-Nov27} Nov28, Nov29, Nov30, Dec01
Total: 4 days.

If we start with Aug 31 instead of Aug 28 then the both schemes produce
the same result - 2 extra-days (because of there is no Nov 31).
Which of these two approaches is better? I don't know.

The 1st code:
declare @d1 datetime, @d2 datetime
set @d1='Mar 31 1978' set @d2='Mar 30 1979'

select
(datediff(mm,@d1,@d2)+case when day(@d2)<day(@d1) then -1
else 0 end)/12 YearCount,

(datediff(mm,@d1,@d2)+case when day(@d2)<day(@d1) then -1
else 0 end)%12 MonthCount,

case when day(@d2)>=day(@d1) then day(@d2)-day(@d1) else
datediff(dd,@d1,dateadd(dd,1-day(dateadd(mm,1,@d1)),
dateadd(mm,1,@d1)))+day(@d2)-1
end DayCount

The 2nd code:
declare @d1 datetime, @d2 datetime
set @d1='Jan 30 1980' set @d2='Mar 27 1983'

select
(datediff(mm,@d1,@d2)+case when day(@d2)<day(@d1) then -1
else 0 end)/12 YearCount,

(datediff(mm,@d1,@d2)+case when day(@d2)<day(@d1) then -1
else 0 end)%12 MonthCount,

case when day(@d2)>=day(@d1) then day(@d2)-day(@d1) else
case when day(dateadd(dd,-day(@d2),@d2))<day(@d1) then
datediff(dd,@d1,dateadd(dd,1-day(dateadd(mm,1,@d1)),
dateadd(mm,1,@d1)))+day(@d2)-1
else
datediff(dd,dateadd(dd,day(@d1)-day(@d2),dateadd(mm,-1,@d2)),@d2)
end
end DayCount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources