SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 old of person
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/14/2013 :  12:04:32  Show Profile  Reply with Quote
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.

Edited by - sigmas on 09/14/2013 12:08:50

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/14/2013 :  14:50:08  Show Profile  Reply with Quote
Something like this:

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];


Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/15/2013 :  15:02:32  Show Profile  Reply with Quote
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)

Singapore
17429 Posts

Posted - 09/16/2013 :  00:01:41  Show Profile  Reply with Quote
See "Finding Age" in this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762&SearchTerms=Finding%20Age


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000