age in char format - 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)

age in char format

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-15 : 21:23:28

Hi friends
any one has a script that returns age in char format. i mean if my DOB is 01/01/2006
i want output like 0 years 2 months .,
Thanks for your help

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-15 : 21:32:58

Why would someone who is only 45 days old be considered 2 months old?

CODO ERGO SUM

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-15 : 21:35:40

sorry Michale.
i typed wrongly in a hurry. but you'll get the drift what i want right ?
Thanks

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-15 : 21:37:23

ok i give an correct example this time
if DOB is 1925-07-01 then output should be something like
80 Years 7 Months 15 Days

Cheers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-15 : 21:41:10

You should be able to adapt the logic in the script I posted for you yesterday for age in years. Just calculate the age in months, and subtract (age in years x 12). I'm sure you get the drift.

Age in years:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61784

CODO ERGO SUM

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-15 : 22:01:15

Thanks Michael. will give it a try.

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-19 : 22:27:15

those who want similar feature can try follwing
select [Age] = convert(varchar, [Years]) + ' years ' +
convert(varchar, [Months]) + ' months ' +
convert(varchar, [Days]) + ' days',
*
from
(
select
[Years] = case when BirthDayThisYear <= Today
then datediff(year, BirthYearStart, CurrYearStart)
else datediff(year, BirthYearStart, CurrYearStart) - 1
end,
[Months]= case when BirthDayThisYear <= Today
then datediff(month, BirthDayThisYear, Today)
else datediff(month, BirthDayThisYear, Today) + 12
end,
[Days]= case when BirthDayThisMonth <= Today
then datediff(day, BirthDayThisMonth, Today)
else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today)
end,
Birth = convert(varchar(10) ,Birth, 121),
Today = convert(varchar(10), Today, 121)
from
(
select BirthDayThisYear = dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth),
BirthDayThisMonth = dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth),
*
from
(
select BirthYearStart = dateadd(year, datediff(year, 0, Birth), 0),
CurrYearStart = dateadd(year, datediff(year, 0, Today), 0),
BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0),
CurrMonthStart = dateadd(month, datediff(month, 0, Today), 0),
*
from
(
-- Load some test date pairs
select Birth = convert(datetime, '1960-02-29'),
Today = dateadd(day, 0, datediff(day, 0, getdate()))
) aaaa
) aaa
) aa
)a

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-19 : 22:41:24

rajani, the above script has a bug with leap year

, it returns 45 years 12 months 23 days for 1960-02-29

----------------------------------
'KH'
Time is always against us

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-02-19 : 22:49:10

Thanks KH.
if i am not wrong u r the one who helped me to find a solution for my question on sql server central site.
Thanks for ur help again.

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-19 : 22:54:05

Ha ha

. This is interesting. I works on the query based on MVJ's code.
However, still have to resolve the leap year problem.

----------------------------------
'KH'
Time is always against us

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources