0

I have a User table which have various details related to User like ID, Name, Profession, Email, Mobile, DOB. I want to write query which should fetch users whose birthday is tomorrow.

Working MySQL query: 

SELECT * 
FROM  user 
WHERE  DATE_ADD(DOB, INTERVAL YEAR(CURDATE())-YEAR(DOB) + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(DOB),1,0) YEAR) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 DAY);

Results:

Eg: ID  | Name  | Profession    | Email         | Mobile    | DOB
    1   | A     |   IT          | a@gmail.com   | 123       | 1987-04-23
    2   | B     |   Student     | b@gmail.com   | 234       | 1987-05-23
    3   | C     |   Actor       | c@gmail.com   | 456       | 1987-06-23

Output: 1   | A     |   IT          | a@gmail.com   | 123       | 1987-04-23

Need help in writing Hibernate or Native query.

-Thanks.

Seemant Shukla
  • 619
  • 1
  • 7
  • 16
  • This does't help you turn it into a hibernate query, but you might try this for the where clause: ```DATE_FORMAT(DOB,'%m/%d') BETWEEN DATE_FORMAT(DATE_SUB(CURRDATE(),INTERVAL 1 DAY),'%m/%d') AND DATE_FORMAT(CURRDATE(),'%m/%d')``` – Sloan Thrasher Apr 22 '18 at 17:01

1 Answers1

0

You can use DATE_ADD() with INTERVAL. Look below query:

SELECT
    *
FROM
    user
where
    date(DOB)= date(
        DATE_ADD(
            now(),
            INTERVAL 1 DAY
        )
    ) and month(DOB)= month(
    DATE_ADD(
        now(),
        INTERVAL 1 DAY
    )
) ;
Dherik
  • 17,757
  • 11
  • 115
  • 164
Lokesh Kumar Gaurav
  • 726
  • 1
  • 8
  • 24
  • Thank U Lokesh for the query but I want the Hibernate/ Native query for the MySQL (as mentioned above - MySQL query is working fine). – Seemant Shukla Apr 22 '18 at 17:56
  • You can use above query by making small change based on your entity class. You need to change in query exact table name and column name then it will become native query. – Lokesh Kumar Gaurav Apr 23 '18 at 05:00