CODEWITHSUNDEEP
sqlhibernatejpahql
Mahmoud Saleh
Mahmoud Saleh

Reputation: 33635

Get birthday greater than current date

my user table is as follows:

  1. birhtMonth: int
  2. dayOfBirthday: int
  3. HireDate: Date

REQUIREMENT: i want to get all upcoming birthdays and hire dates (day/month year is excluded here) in next 6 months, putting in consideration that for current month the day should be greater than today, so here's what i did:

@Query("from User u where ( (u.birthMonth in (8,9,10,11,12,1)) or (month(u.hireDate) in (8,9,10,11,12,1)) ) and u.company = :company")

this gets all upcoming birthdays & hire dates in next six months but it gets birthdays & hire dates in this month for days before & after current day, and it should only get results > current day and ignore results < current day in this month.

EXAMPLE:

they should be ignored too, please advise how to solve this in sql or hql.

Upvotes: 0

Views: 1880

Answers (2)

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7322

Your hireDate is of type Date, so use a date comparison and use between such as:

(hireDate between :toDayParam and :sixMonthLaterParam)

for birthDate, you can compare with lpad(birthMonth, 2, 0) + lpad(birthDate, 2, 0) but you shall care about whether six month later is in next year or current year.

Upvotes: 1

user2647501
user2647501

Reputation: 1

I think your looking for the DATE functions. They can greatly help you out here. Specifically the DATEADD function. Take a look at the code I made here.

SELECT * FROM dbo.product 
WHERE dbo.product.stockDate > GETDATE()
AND dbo.product.stockDate < GETDATE() + DATEADD(month, 6, dbo.product.stockDate)
AND dbo.product.expirationDate > GETDATE()
AND dbo.product.expirationDate < GETDATE() + DATEADD(month, 6, dbo.product.expirationDate)

This will guarantee that the stockDate and the expirationDate are greater than the current date and less than the current date + 6 mo. DATEADD works as follows DATEADD(-what you want to increment by-, -how much you want to increment-, -date to add to-).

Upvotes: 0

Related Questions