I'm looking for a way to filter two date (year) with the operator between into my sql request but I can't do it. I have difficulties to integrate "Between" into "InnerJoin" I want to filter on the "Entry_Date" column with "Between" based on the year.

This an example of my table : Patients

This is my SQL Request:

SELECT ALL Patients.Code, Patients.Name, Patients.FirstName FROM Patients 
INNER JOIN Pays ON Patients.CountryCode = CountryCode.Code
INNER JOIN Sex ON Patients.Sex = Sex.Code
INNER JOIN ReasonCode ON Patients.ReasonCode = ReasonCode.Code
WHERE Patients.Name LIKE  '".$namePatient."%'
AND Patients.ReasonCode LIKE '".$ReasonCode."%'
AND Patients.CountryCode LIKE '".$CountryCode."%'
ORDER BY Patients.Name ASC

Thank in advance for your help.

CodePudding user response：

if you'd like to filter on year you don't need a BETWEEN; you could use the year function:

SELECT ALL Patients.Code, Patients.Name, Patients.FirstName FROM Patients 
INNER JOIN Pays ON Patients.CountryCode = CountryCode.Code
INNER JOIN Sex ON Patients.Sex = Sex.Code
INNER JOIN ReasonCode ON Patients.ReasonCode = ReasonCode.Code
WHERE Patients.Name LIKE  '".$namePatient."%'
AND Patients.ReasonCode LIKE '".$ReasonCode."%'
AND Patients.CountryCode LIKE '".$CountryCode."%'
AND YEAR(entry_date) = {DesiredYear}
ORDER BY Patients.Name ASC

As pointed out by Pred the comments below using a function in the where clause could result in the query not making use of an index depending on your platform

CodePudding user response：

Bypassing the discussions around predicates involving expressions instead of column values or literals -

It's in any case good practice to avoid using expressions in filter and join predicate on any DBMS. Just as well as I never rely on a DBMS to implicitly cast the string '42' to an integer.

Liberté, égalité, portabilité . You might need your SQL code in a new SQL platform, so make sure you're the master and not the victim of what the DBMS does.

So I'd calculate the end of the year of the input date, the beginning of the year of the input date, and use a BETWEEN predicate.

Like so:

WHERE search_dt
BETWEEN 
  DATE_ADD(
    LAST_DAY(
      DATE_ADD(entry_date,INTERVAL MONTH(entry_date)*-1 MONTH)
    )
  , INTERVAL 1 DAY
  ) 
AND 
  LAST_DAY(
    DATE_ADD(entry_date,INTERVAL 12-MONTH(entry_date) MONTH)
  )