age in char format
- Hi friendsi need ur advise on this one.i was looking for a script that translates a person age in char (i.e. 9 years 6 months etc.,)and i found a script on sqlservercentral.com. the script can b found hereits working fine as expected but its failing if date of birth is1960-02-29 00:00:00.000the error is
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
following code of the actual script is causing problemIF CAST(CONVERT(VARCHAR(2),MONTH(@l_dtPrevDate)) + '/' + CONVERT(VARCHAR(2),DAY(@l_dtPrevDate)) + '/' + CONVERT(VARCHAR(4),YEAR(@l_dtCurrentDate)) AS DATETIME ) > @l_dtCurrentDateBEGIN
SET @years = @years - 1
END
i cant figure out whats the problem can anyone help ?Thanks -
Change to YYYY/MM/DD. Should always use YYYY/MM/DD or YYYYMMDD to avoid locale dependend conversion
IF CAST(CONVERT(VARCHAR(4),YEAR(@l_dtPrevDate)) + '/' + CONVERT(VARCHAR(2),MONTH(@l_dtPrevDate)) + '/' + CONVERT(VARCHAR(2),DAY(@l_dtCurrentDate)) AS DATETIME ) > @l_dtCurrentDate
-
Alternate Method :
select [Age] = convert(varchar, [Years]) + ' years ' +
convert(varchar, [Months]) + ' months ' +
convert(varchar, [Days]) + ' days',
*
from
(
select
[Years] = casewhen BirthDayThisYear <= Today
then datediff(year, BirthYearStart, CurrYearStart)
else datediff(year, BirthYearStart, CurrYearStart) - 1
end,
[Months]= casewhen BirthDayThisYear <= Today
then datediff(month, BirthDayThisYear, Today)
else datediff(month, BirthDayThisYear, Today) + 12
end,
[Days]= casewhen 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
(
selectBirthDayThisYear = dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth),
BirthDayThisMonth = dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth),
*
from
(
selectBirthYearStart = 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
selectBirth = convert(datetime, '1960-02-29'),
Today = dateadd(day, 0, datediff(day, 0, getdate()))
) aaaa
) aaa
) aa
)a
- Thank you very much for this 'KH'i tried ur first suggestion which resolved the error and when i run the script i get following result46 Years -1 Months 23 Daysdid u see "-1" ?i also tried ur script and i get following result45 years 12 months 23 dayswhy its not displaying 46years bcoz its 12 months ?have i mis understood anything ?
-
>> i also tried ur script and i get following result
>> 45 years 12 months 23 days
it's a bug with leap year. There are only 28 days in 2006-02.
- Thanks for that KH.BTW am using ur script as it more understandable to read and follow.
-
It was based on MVJ's original script
You can refer to here
-
You could also try this:
declare @days int
select @days =
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
select
cast(datediff(mm, @date1 + @days, @date2)/12 as varchar) + ' years ' +
cast(datediff(mm, @date1 + @days, @date2)%12 as varchar) + ' months ' +
cast(@days as varchar) + ' days'
-
Select Getdate(), DateBirth, AGE, cast (datepart(YY, AGE)-1900 as varchar(2)) + ' 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 - sergiywhat should the variable @DateBirth contain ?
-
Sorry, forgot to remove "@"
_____________
Code for TallyGenerator -
Serqiy,
The only problem I see is that there are folks that are over 99 years... just change the VARCHAR(2) to VARCHAR(3) and you're golden because you accounted for everything else including the extra leap year that occurred in 2000.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Serqiy,i tried removing @ but getting error DateBirth does not exist
- sorry my mistakemy column name birthdate not birthdate in my table
it works like charm - Serqiy,can you kindly explain ur sql statement so that i understand logic behind it .Thanks for ur help .
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply