Date Calculation - SQL Server Forums

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

Transact-SQL (2000)

Date Calculation

Author

Topic

homerjay80
Starting Member

26 Posts

Posted - 2007-03-17 : 02:36:23

Hello,

I am trying to write a select statment that takes the dateofbirth, determines the exact age on the last day of the current year.

Ex: Some one born on 07/07/1980 would be 27 on 12/31/2007
and 28 on 12/31/2008, etc.

I have tried using the standard DateDiff function and had no sucess

Any help would be appreaciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-17 : 02:40:11

take a look here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762&SearchTerms=Finding%20Age

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 02:47:56

Exact age in what?
Weeks? Years? Months? Or all of them?

Also see here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Peter Larsson
Helsingborg, Sweden

homerjay80
Starting Member

26 Posts

Posted - 2007-03-17 : 13:57:31

Sorry, Exact Age in Years
Can this be done without creating a function.
I am trying to do return it in a select query.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 14:05:52

[code]SELECT *,
DATEDIFF(month, DoB, '20071231') / 12 AS YearsOfAge
FROM Employees[/code]
Peter Larsson
Helsingborg, Sweden

homerjay80
Starting Member

26 Posts

Posted - 2007-03-17 : 14:52:36

Is there way to do this without declaring variables.
Here is an example where I get close, but can't get the second column to project age on the last day of the year.

select id, dateofbirth, (Datediff(YEAR, dateofbirth, getdate())) as CurrentAge,
Datediff(YEAR, dateofbirth, (select convert(char(10), (select dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))), 101)))as AgeOn1231
from table

homerjay80
Starting Member

26 Posts

Posted - 2007-03-17 : 16:08:47

Peter,
I still don't think its working. I added to what you gave me to show you a comparison of the employees age today and the employees and on 12/31 given their date of birth. Do you know why I can't get this to work.

SELECT	id,dateofbirth, (Datediff(YEAR, dateofbirth, getdate())) as CurrentAge,
	CASE
		WHEN DATEPART(day, DateofBirth) = 31 THEN DATEDIFF(month, DateofBirth, '20071231') - 1
		ELSE DATEDIFF(month, dateofbirth, '20071231')
	END / 12 AS YearsOfAge
FROM	emppers

id      dateofbirth                                                  CurrentAge  YearsOfAge  
------------ ------------------------------------------------------ ----------- ----------- 
1HHIC5000080 1935-04-11 00:00:00.000                                72          72
1HHTN6000080 1950-06-02 00:00:00.000                                57          57
1HHZTS000080 1960-01-01 00:00:00.000                                47          47
1J64ZT000080 1949-12-08 00:00:00.000                                58          58
1J6G9U000080 1958-05-08 00:00:00.000                                49          49
1J6KNB000080 1960-08-19 00:00:00.000                                47          47
1HHRYQ000080 1960-02-25 00:00:00.000                                47          47
1HHI8N000080 1972-12-19 00:00:00.000                                35          35
1HHIM7000080 1935-11-02 00:00:00.000                                72          72

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 16:13:18

Where is the problem?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 16:18:22

DATEDIFF works according to your INTERVAL setting!

If you choose YEAR, SQL Server calculate the year from start to finish. COMPLETE YEARS.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 16:30:41

In the original posting you said "How many years per last day of year?".
Then standard DATEDIFF(YEAR, DoB, '20071231') is sufficient.

But, in you most recent post, you want to calculate the exact age per today!
What is it? What do you want?

If you want exakt age per today, you use

SELECT	DoB,
	CASE
		WHEN DATEPART(DAY, DoB) > DATEPART(DAY, CURRENT_TIMESTAMP) THEN DATEDIFF(MONTH, DoB, CURRENT_TIMESTAMP) - 1
		ELSE DATEDIFF(MONTH, DoB, CURRENT_TIMESTAMP)
	END / 12 AS YearsOfAge
FROM	Employees

Peter Larsson
Helsingborg, Sweden

homerjay80
Starting Member

26 Posts

Posted - 2007-03-17 : 16:55:29

Ok, I see your point and it does work how you say. Now I have just one other question. Is there a way not to hard code the the date '20071231' so it wouldn't have to be changed every year?
I appreciate your assistance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-17 : 19:12:00

quote:
Originally posted by homerjay80

Ok, I see your point and it does work how you say. Now I have just one other question. Is there a way not to hard code the the date '20071231' so it wouldn't have to be changed every year?
I appreciate your assistance.

You mean find the "end of the year"'s date ?


select dateadd(year, datediff(year, 0, getdate()) + 1, -1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources