SQLTeam.com | Weblogs | Forums

Assistance in sql for oracle self service

hello
i am new to sql and i am a business analyst i have a requirement to calculate the age of a person from his date of birth
i have created descriptive flexfield for the birthdate with segment value FND_Standard_Date.
and a second field titled Age which should give me the age of the person based on the previous date of birth field filled by the user.
Can anyone extend some help in this
Many thanks in advance have a good day

Welcome. Please always provide sample data when asking questions.

if OBJECT_ID('tempdb..#cate') IS NOT NULL
	drop table #cate;

create table #cate(name varchar(50), date_of_birth date)

insert into #cate
select 'Darth Vader', '1968-01-23' union
select 'Cathy', '1980-01-01'


select *, DATEDIFF(yyyy,date_of_birth, getdate())
  from #cate

Yosiasz, You have to be careful using year. Today is 3/16. If my birthday isn't til 3/17, your calc shows me as already having my birthyday.

if OBJECT_ID('tempdb..#cate') IS NOT NULL
	drop table #cate;

create table #cate(name varchar(50), date_of_birth date)

insert into #cate
select 'Darth Vader', '1968-01-23' union all
select 'Cathy', '1980-01-01' union all
select 'Before', '1980-03-15' union all
select 'Today', '1980-03-16' union all
select 'Tomorrow', '1980-03-17'

select *, 
		DATEDIFF(yyyy,date_of_birth, getdate()) - 
 			Case when DatePart(DY, date_of_birth) > DatePart(DY, getdate()) then 1 else 0 end

  from #cate

thanks Mike, maybe you have a twin in the other universe who already had his birthday. :laughing: