age in char format
-
Full script I used for my test:
DECLARE @Table TABLE (
Datebirth datetime )
INSERT INTO @Table
SELECT '1950-02-21'
Select Getdate(), DateBirth, AGE, cast (datepart(YY, AGE)-1900 as varchar(3)) + ' Years, '
+ cast(datepart(mm, AGE)-1 as varchar(2) ) + ' Months, '
+ cast(datepart(dd, AGE)-case when DateBirth < '2000-03-01' then 2 else 1 end as varchar(2)) + ' Days'
from (select DateBirth, Getdate() - DateBirth as AGE FROM @Table) dt
_____________
Code for TallyGenerator -
And actually it's not right.
It's good for partial year calculations (say, 75.3 year) not for days.
Right one is here:
Select DateBirth, cast (Years as varchar(3)) + ' Years, '
+ cast(Months + case when Days < 0 then 1 else 0 end as varchar(2) ) + ' Months, '
+ cast(Days + case when Days < 0 then DaysInLastMonth else 0 end as varchar(2)) + ' Days'
FROM (
Select Datebirth, datepart(YY, Getdate())-datepart(YY, Datebirth) as Years,
(datepart(mm, Getdate())-datepart(mm, Datebirth)+12)%12 as Months,
datepart(dd, Getdate())-datepart(dd, Datebirth) as Days,
datediff(dd, Datebirth, dateadd(mm, 1, Datebirth)) as DaysInLastMonth
from @Table
) DT
_____________
Code for TallyGenerator -
This doesn't work: '20060131' returns 0 years, 2 months, 18 days (today is '20060221'), while the day after, '20060201', correctly returns 0 years, 0 months, 20 days.
My suggestion may still be worth considering:
DECLARE @Table TABLE (
Date1 datetime, Date2 datetime )
INSERT INTO @Table SELECT '2006-01-26', getdate()
INSERT INTO @Table SELECT '2006-01-27', getdate()
INSERT INTO @Table SELECT '2006-01-28', getdate()
INSERT INTO @Table SELECT '2006-01-29', getdate()
INSERT INTO @Table SELECT '2006-01-30', getdate()
INSERT INTO @Table SELECT '2006-01-31', getdate()
INSERT INTO @Table SELECT '2006-02-01', getdate()
select date1, date2,
cast(datediff(mm, dt.date1 + dt.days, dt.date2)/12 as varchar) + ' years ' +
cast(datediff(mm, dt.date1 + dt.days, dt.date2)%12 as varchar) + ' months ' +
cast(dt.days as varchar) + ' days'
from
(
select
date1 as date1,
date2 as date2,
case when datepart(d, date1) <= datepart(d, date2)
then
datepart(d, date2) - datepart(d, date1)
else
datediff(d, date1 - datepart(d, date1) + 1, dateadd(m, 1, date1 - datepart(d, date1) + 1)) -- Number of days this month
+ datepart(d, date2) - datepart(d, date1)
end
as days
from @Table
)
dt
One should however note that it is not completely clear what the answer should be. What is the difference between Feb 28th and Apr 1st? 1 month 4 days (add 1 month to get Mar 28th, then add 4 days to get Apr 1st) or 1 month 1 day (add 1 day to get Mar 1st, then add 1 month). In my solution I add days before months.
-
Run the following Given Procedure :--
Create procedure AGE(@DT AS DATETIME)
AS
SELECT convert(varchar,datediff(dd,convert(datetime,@DT),convert(datetime,convert(varchar,getdate(),101))) /365) + ' Years'
+ ' '
+ convert(varchar,(datediff(dd,convert(datetime,@DT),convert(datetime,convert(varchar,getdate(),101)))-
(datediff(dd,convert(datetime,@DT),convert(datetime,convert(varchar,getdate(),101)))/365)*365) / 30) + ' Months'
+ ' '
+ convert(varchar,(datediff(dd,convert(datetime,@DT),convert(datetime,convert(varchar,getdate(),101)))-
(datediff(dd,convert(datetime,@DT),convert(datetime,convert(varchar,getdate(),101)))/365)*365) -
((datediff(dd,convert(datetime,@DT),convert(datetime,convert(varchar,getdate(),101)))-
(datediff(dd,convert(datetime,@DT),convert(datetime,convert(varchar,getdate(),101)))/365)*365) / 30) * 30) + ' Days'
and pass your date of birth into it. It will Provide Your current age.
Exec Age('1982-12-18')
-
'2004-02-21' returns 2 years 0 months 1 day and I would expect it to return 2 years, 0 months, 0 days. Similarly, '2006-01-21' returns 1 month, 1 day and I would expect it to return 1 month. Tested at 2006-02-21.
The problem is that not every month has 30 days. And not every year has 365 days.
- DudesThanks all for trying to help.now am confused which suggestion to take !!looks like i need to do more testing before finalising which script to use.i have too many options
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply