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]
Go to Top of Page

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);
Go to Top of Page

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]

Go to Top of Page
   

- Advertisement -