Why does the query by date range not work when different years are involved?

Question

I have a query for date range in Laravel that brings me to my head. Initially the query works without errors but I have detected that if the date range is between different years (12-2020 to 01-2021) it does not return any records. I'm a little tired of checking the code and I don't see the problem.

I put here the query that saves the data in an array and the check that I do later to apply the filter of the dates before returning the values.

public function getData($filters) {

    $tasks = $this->getModel()
        ->whereIn('tasks.department_id', $filters['departmentIds'])
        ->join('departments', 'departments.id', '=', 'tasks.department_id')
        ->join('deliverables as dev', 'dev.id', '=', 'tasks.deliverable_id')
        ->leftJoin('deliverables as sub', 'sub.id', '=', 'tasks.subdeliverable_id')
        ->leftJoin('workers as st_workers', 'st_workers.id', '=', 'tasks.start_worker_id')
        ->leftJoin('workers as end_workers', 'end_workers.id', '=', 'tasks.end_worker_id')
        ->leftJoin('type_task_statuses', 'type_task_statuses.id', '=', 'tasks.status_id')
        ->leftJoin('incidences', 'incidences.task_id', '=', 'tasks.id')
        ->select('tasks.id', 'departments.name as department', 'tasks.start_worker_id', 'tasks.end_worker_id', 'st_workers.short_name as start_worker',
        'end_workers.short_name as end_worker', 'tasks.plane', 'tasks.st', 'dev.name as deliverable', 'sub.name as subdeliverable',
            'tasks.quantity', 'tasks.other_deliverable', 'tasks.start_prevision_date', 'tasks.end_prevision_date', 'tasks.start_date',
            'tasks.end_date', 'tasks.prevision_hour', 'tasks.incurred_hour', 'type_task_statuses.display_name as status', 'type_task_statuses.color', 'tasks.advance', 'tasks.agreed',
            'tasks.disagreed_date', 'tasks.supervised', DB::raw('COUNT(incidences.id) as count_incidences'))
        ->groupBy('tasks.id');

    if (array_key_exists('fromDate', $filters) && $filters['fromDate']) {
        $tasks->whereRaw('(DATE_FORMAT(tasks.end_date, "%m-%Y") >= "'.$filters['fromDate'].'" OR tasks.end_date is NULL)');
    }
    if (array_key_exists('toDate', $filters) && $filters['toDate']) {
        $tasks->whereRaw('(DATE_FORMAT(tasks.end_date, "%m-%Y") <= "'.$filters['toDate'].'" OR tasks.end_date is NULL)');
    }

    return $tasks;
}

You are comparing your dates as mm-YYYY. That means that you are not really comparing dates but strings, and as string 12-2020 is greater than 01-2021. You shold compare dates in the format yyyy-mm, so you would compare 2020-12 with 2021-01 — nacho, Feb 15 '21 at 11:03
So you shold chnage the format of **$filters['toDate'] and $filters['fromDate']** to yyyy-mm format — nacho, Feb 15 '21 at 11:04
Hi Nacho, if I apply the change as you indicate according to the MySql specifications for Date_Format, I don't get results either, I still get an empty query. **$tasks->whereRaw('(DATE_FORMAT(tasks.end_date, "%Y-%m") >= "'.$filters['fromDate'].'" OR tasks.end_date is NULL)');** — jmrufo, Feb 15 '21 at 11:25
With that DATE_FORMAT instruction you are converting your date to the format yyyy-mm, so 2020-01-01 converts to 2020-01. You have the problem in your $filters['toDate'] variable, that is in format mm-yyyy. This is the one you need to change to yyyy-mm (and $filters['fromDate'] too) — nacho, Feb 15 '21 at 11:49
Can you echo (or print) the value you have in $filters['toDate']?? — nacho, Feb 15 '21 at 12:06

score 0 · Accepted Answer · answered Feb 15 '21 at 16:16

I would suggest to format your input to date range in your application rather than applying formatting on database side see my another answer for anit-patterns

Let say you have following input to match the date in database

$filters['fromDate'] ='12-2020';
$filters['toDate'] = '01-2021';

Which is equivalent to following date range in actual

From 2020-12-01 00:00:00 To 2021-01-31 23:59:59

So lets try to convert the filters input to this range format

/** 2020-12-01 00:00:00 */
$fromDate = \Carbon\Carbon::createFromFormat('d-m-Y', '01-'.$filters['fromDate'])->startOfMonth()->format('Y-m-d H:i:s');
/** 2021-01-31 23:59:59 */
$toDate = \Carbon\Carbon::createFromFormat('d-m-Y', '01-'.$filters['toDate'])->endOfMonth()->format('Y-m-d H:i:s');

Now you can easily apply these ranges in your query and on database side you wont need any kind of formatting

if (array_key_exists('fromDate', $filters) && $filters['fromDate']) {
    $fromDate = \Carbon\Carbon::createFromFormat('d-m-Y', '01-' . $filters['fromDate'])->startOfMonth()->format('Y-m-d H:i:s');
    $tasks->where(function ($query) use ($fromDate) {
        $query->whereNull('tasks.end_date')
              ->orWhere('tasks.end_date', '>=', $fromDate);
    });
}

if (array_key_exists('toDate', $filters) && $filters['toDate']) {
    $toDate = \Carbon\Carbon::createFromFormat('d-m-Y', '01-' . $filters['toDate'])->endOfMonth()->format('Y-m-d H:i:s');
    $tasks->where(function ($query) use ($toDate) {
        $query->whereNull('tasks.end_date')
              ->orWhere('tasks.end_date', '<=', $toDate);
    });
}

Thank you very much for the answer and clarification. Now it works perfectly. Thanks — jmrufo, Feb 15 '21 at 16:38
A doubt Khalid, could you tell me what the 01 indicates in front of the filter? **'01 - '. $ Filters [' fromDate ']** — jmrufo, Feb 15 '21 at 16:52
@jmrufo Its a default value to create a date lateral d-m-y from month year format m-y and its nothing to worry about because we are picking first and last day from carbon object `->startOfMonth(), ->endOfMonth()` — M Khalid Junaid, Feb 15 '21 at 17:02

Why does the query by date range not work when different years are involved?

1 Answers1