Calculating age using date of birth

Support
#1

hi Cuba team,
i m new to Cuba. i have one doubt in jpql how can i calculate age using Date of birth .
below i mentioned my query

select NAME as name,
PROFILE_DOB as dob,
YEAR(CURDATE()) - YEAR(PROFILE_DOB) AS AGE 
from matrimony$ProfileDetails
where profileStatus=2 AND active=true
#2

Hi. I don’t know how to do what you want in JPQL but I might have an alternative that works for you. You could create a meta property in your ProfileDetails entity and calculate the age in Java. Then you just have to call profileDetailsEntity.getAge(). Something like this in your entity may work.

@MetaProperty(related={"profileDob"})
public int getAge() {
    LocalDate birthDate = profileDob.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
    LocalDate now = LocalDate.now();
    return Period.between(birthDate, now).getYears();
}
1 Like
#3

I think you need something like:

select m.name, 
m.profileDob,
extract(year from CURRENT_DATE) - extract(year from m.profileDob) as age
from matrimony$ProfileDetails m
where m.profileStatus = 2 and m.active = true

Your syntax looks a little bit odd to me; I think you are mixing standard SQL with JPQL. In JPQL you work with the entity class names rather than the database field names.

It might be worth having a look through some of the other Cuba examples, as well as these pages:

https://doc.cuba-platform.com/manual-latest/query.html
https://doc.cuba-platform.com/manual-latest/jpql_functions.html

And if you’re going to be using DoB a lot, then it’s better to do what @Keith suggested and use a meta property on the entity.

#4

thank you so much : ) @ Keith

#5

thank you so much : ) @ ray1