old of person - 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 2012 Forums

Transact-SQL (2012)

old of person

Author

Topic

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-14 : 12:04:32

Hi,
We have to value, birth date and today date.
How can we calculate years, months and days after a date (old age)?

for example according to following values(yyyymmdd) we need these values:
12 years - 10 months - 2 days

select '2000-05-29' as bithdate,'2013-04-01' as today_date

Please direct me.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-14 : 14:50:08

Something like this:
[CODE]
SELECT '2000-05-29' as bithdate,'2013-04-01' as today_date,
DATEPART(YEAR, DATEADD(DAY, 0, DATEDIFF(DAY, '2000-05-29','2013-04-01'))) - 1900 AS [YEARS],
DATEPART(MONTH, DATEADD(DAY, 0, DATEDIFF(DAY, '2000-05-29','2013-04-01')))-1 AS [MONTHS],
DATEPART(DAY, DATEADD(DAY, 0, DATEDIFF(DAY, '2000-05-29','2013-04-01')))-1 AS [DAYS];

[/CODE]

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-15 : 15:02:32

Thank you very much.
I think no need to DATEADD function. It automatically calculate adding the days with base date.


--Simplified
SELECT 
	DATEPART(YEAR, DATEDIFF(DAY, bithdate,today_date)) - 1900 AS [YEARS],
	DATEPART(MONTH, DATEDIFF(DAY, bithdate,today_date))-1 AS [MONTHS],
	DATEPART(DAY, DATEDIFF(DAY, bithdate,today_date))-1 AS [DAYS],
	DATEPART(DAY, DATEDIFF(DAY, bithdate,today_date))-1 AS [DAYS]
FROM 
(
VALUES('2000-05-31', '2013-04-01'),--12-10-2
('1990-01-02','2013-10-30'),--23-9-28
('2010-01-01','2011-01-01')
)D (bithdate, today_date);

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-16 : 00:01:41

See "Finding Age" in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762&SearchTerms=Finding%20Age

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources