I have a visits table

visits
dob[varchar(50)]    visitdate[Date]           name[varchar(16)]    
09/16/2001          2022-11-01                             A
09/26/1966          2022-11-01                             B
09/21/1999          2022-11-02                             C
09/24/2000          2022-11-02                             D

I need to get records of those below the age of 24 by the visit date

Currently, I am using the filter to get records

Here are below code

function get_age_group($dob, $visitDate)
{
    $dob        = explode("/", $dob);
    $dob        = new DateTime($dob[2] . '-' . $dob[0] . '-' . $dob[1]);
    $visitDate  = new DateTime($visitDate);
    $diff       = $visitDate->diff($dob);
    return $diff->y;
}

// here is query
$visits = DB::table('visits')->get();

foreach ($visits as $row)
{
    $age = get_age_group($row->dob, $row->visitdate);
    if(($age < 24))
    {
        //push the data
    }
}

I want to get all records where the age is under 24 between two dates(dob, visitdate)

// here is query $visits = DB::table('visits')->where(?)->get();

I want to remove the above foreach function and want to add where clause in the query to get the records under 24 years.

CodePudding user response：

CREATE TABLE visits (dob VARCHAR(50), visitdate DATE, name VARCHAR(16));
INSERT INTO visits VALUES
('09/16/2001',          '2022-11-01',                             'A'),
('09/26/1966',          '2022-11-01',                             'B'),
('09/21/1999',          '2022-11-02',                             'C'),
('09/24/2000',          '2022-11-02',                             'D');

SELECT *
FROM visits
WHERE STR_TO_DATE(dob, '%m\/%d\/%Y')   INTERVAL 24 YEAR > visitdate;

fiddle

If DOB contains incorrect date value (including the value with excess leading chars) then according row won't be returned. I recommend you to alter this column's datatype to DATE.