ssis expression- calculate age by using birthdate

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

You might try:

declare @dob datetime,

@age int,

@day datetime

set @day = GETDATE()

set @dob = '1932-04-18'

set @age = datediff(yy,@dob,@day) -

case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end

select @age

Result:

79

If my memory serves me correctly I think the above code was written by Steve Jones, a long long time ago.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, this works in stored procedures or SQL.

What I would like to use is in the SSIS conditional split editor, to filter out their birthdate <20 years old.

The original oracle query is like this, how can I replace it with SQL ones.

ROUND((months_between(SYSDATE,BIRTH_DATE))/12)<20

Thanks

Eric SSCommitted Points: 1803 More actions · Answer 3

In the conditional split you could compare the birth date to today's date minus 20 years.

BirthDate > DATEADD("yy",-20,@[System::StartTime])

Just be sure to watch your boundary conditions. @[System::StartTime] is date/time and I'm guessing birth date is just days.

raghu-sql Mr or Mrs. 500 Points: 517 More actions · Answer 4

I think we can also use derived column transformation and use the datediff() function and system time

to get the age .