How to fetch date from db and compare it with age

Question

I am storing date of birth in this format DD/MM/YYYY (25/03/1984) in database. I have a search form in my application where users can provide from age and to age to get list of profiles which fall between the age limit. How to handle this situation.

Do i need to store age directly in db (But age will change every year)
Do i need to fetch dates from DB and employ java logic to get age from DOB and compare it with the from date and to date.( I really don't want java logic.It is a long route. I am interested in other solutions which makes it much easier.)

Field         | Type
user_id       | int(11)
name          | varchar(100)
mobile_number |varchar(45)
date_of_birth |varchar(45)

user_id| name  | mobile_number | date_of_birth
   1   | Test1 | 1234567890    | 25/03/1984
   2   | Test2 | 1234567890    | 25/03/1980
   3   | Test3 | 1234567890    | 25/03/1988
   4   | Test4 | 1234567890    | 25/03/1970

Could you please suggest a best way to handle this situation.

[How can I determine if a date is between two dates in Java?](http://stackoverflow.com/questions/883060/how-can-i-determine-if-a-date-is-between-two-dates-in-java) — Naman Gala, Oct 06 '15 at 05:29
I'm using mysql db. The data type is varchar. I am using hibernate and spring in my application. — Thiagarajan Ramanathan, Oct 06 '15 at 14:22

score 2 · Answer 1 · answered Oct 06 '15 at 05:42

2

store the birthday in natural database format

hibernate:

@Temporal(DATE)
private Date birthday;

Then you can query for the entities by using less and greater then other dates

SELECT u FROM USER u where u.birthday > :fromDate and u.birthday <= :toDate

answered Oct 06 '15 at 05:42

Ralph

118,862
56
287
383

Search criteria will be age and not date. i.e. 23 - 30. I need to find age from DOB and compare it with the search criteria. – Thiagarajan Ramanathan Oct 06 '15 at 14:27
fromDate = DateUtil.addYears(now, -1 * (age + 1); toDate = DateUtil.addYears(now, -1 * age)); (DateUtil is part of commons lang lib: https://commons.apache.org/proper/commons-lang/javadocs/api-2.6/org/apache/commons/lang/time/DateUtils.html) – Ralph Oct 06 '15 at 17:58

score 0 · Answer 2 · answered Oct 06 '15 at 05:40

The below query could solve your expectation..

SELECT TRUNC(months_between(sysdate,dob)/12) YEAR,
  TRUNC(mod(months_between(sysdate,dob),12)) MONTH,
  TRUNC(sysdate-add_months(dob,TRUNC(months_between(sysdate,dob)/12)*12+TRUNC(mod(months_between(sysdate,dob),12)))) day
FROM
  (SELECT to_date('15-12-2000','DD-MM-YYYY') dob FROM dual
  );

Year Month day
  14    9   21

score 0 · Answer 3 · answered Oct 06 '15 at 20:50

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(STR_TO_DATE(u.date_of_birth, '%d/%m/%Y'), '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < 
DATE_FORMAT(STR_TO_DATE(u.date_of_birth, '%d/%m/%Y'), '00-%m-%d')) AS age,name,mobile_number from user u where DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(STR_TO_DATE(u.date_of_birth, '%d/%m/%Y'), '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < 
DATE_FORMAT(STR_TO_DATE(u.date_of_birth, '%d/%m/%Y'), '00-%m-%d')) between 30 and 35;

I found the solution. Can someone optimize this query. How to implement this with hibernate.

How to fetch date from db and compare it with age

3 Answers3