sql server dts: Age Calculation -- sql server dts

Re: Age Calculation

Robert Hamilton
3/30/2005 2:45:04 PM

It would seem that my statement isn't convertible to DateTime, but it is in a
Select statement. It does work with QA as a Select statement, but not an
Update statement.

In addition, I ended up adding the statement to the DTS extract that
populates my CaseFindings table. Since the Transform Data Task source is a
Select statement--at least, it is in this case--the trasformation to the
destination table work fine. I would like to know why I can't do this with an
update query, though.

Is there another way of taking a datetime field and calculate age in a SQL
statement?

Thanks so much for your help on this.

Regards,
Rob

[quoted text, click to view]

Re: Age Calculation

Allan Mitchell
3/30/2005 9:29:50 PM

It would seem then that your statement

Cast(LTrim(Str(Year(GetDate())))
+ '-' + Ltrim(Str(Month(CaseFindings.Birth)))
+ '-' + Ltrim(Str(Day(CaseFindings.Birth))) AS
DateTime)

May not be convertible to DateTime

What I would do is grab the output from the above and put it into QA

select cast('YYYY-MM-DD' as datetime)

does It work in QA?

Are you on different boxes?

"Robert Hamilton" <RobertHamilton@discussions.microsoft.com> wrote in
message news:RobertHamilton@discussions.microsoft.com:

[quoted text, click to view]

Re: Age Calculation

Robert Hamilton
3/31/2005 12:15:05 PM

As it turns out, the issue I'm having is related to leap day and me taking
the current year (2005) and concatenating it to a persons leap year birthday
(02-29) to come up with 2005-02-29, which isn't a real date. I do this in
order to compare today, GetDate(), to their birthday this year to determin if
it has past. If it has past, I have to subtract 1 from the DateDiff in order
to get their real birthday. I'm guessing I'll have to test for Feb. 29th
birthdays and artificially make them the Feb. 28th to do my test. Is there a
better way of calculating a persons real age using SQL?

[quoted text, click to view]

Re: Age Calculation

Robert Hamilton
3/31/2005 3:05:07 PM

Thanks Allan, you're awesome for tracking this down for me. I had to change
the THEN to -1 and the ELSE to 0, but it works perfectly now. Please thank BP
Margolin for me as well. Below is the logic I finally used:

SELECT
DateDiff (Year, {d '1967-03-22'}, GetDate( ) ) +
CASE
WHEN DatePart (dayofyear, {d '1967-03-22'}) >
DatePart(dayofyear, GetDate())
THEN -1
ELSE 0
END AS "Age"

Best of regards,
Rob
[quoted text, click to view]

Re: Age Calculation

Allan Mitchell
3/31/2005 6:15:52 PM

Can you pass us your two table structures and a row of data that fails
so we can make our repro and help better?

Thanks

Allan

"Robert Hamilton" <RobertHamilton@discussions.microsoft.com> wrote in
message news:RobertHamilton@discussions.microsoft.com:

[quoted text, click to view]

Re: Age Calculation

Allan Mitchell
3/31/2005 8:40:41 PM

How about this from BP Margolin

create table Robert (c1 datetime)
go

insert into Robert values ('jan 1 1965')
insert into Robert values ('apr 1 1965')
insert into Robert values ('jul 1 1965')
insert into Robert values ('oct 1 1965')

select Birthdate = c1,
Age = datediff (yy, c1, getdate( ) ) +
case when datepart (dy, c1) > datepart(dy, getdate(
) )
then 0
else 1
end
from Robert
order by c1

..

"Robert Hamilton" <RobertHamilton@discussions.microsoft.com> wrote in
message news:RobertHamilton@discussions.microsoft.com:

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.