I have assigned shifts for the employee with date range.
I have to list the shifts which are assigned for a particular month, say october.
I will assign the shift with a shift start date and shift end date.
Say shift start date is oct 28 and shift end date is nov 4.
Now if want to display the shifts allocated for the month of november, the shift in this date is not being displayed.
I have checked it using
shiftStartDate>=oct 1
and
shiftEndDate<=oct31
and i also tried using between query.
This works fine if the shift start and end dates are in the same month.
You could make use of the BETWEEN function in mysql to select data where a specific date is between 2 given dates (i.e. your shiftStartDate and shiftEndDate column).
Example:
SELECT *
FROM
example_table
WHERE
(example_date BETWEEN shiftStartDate AND shiftEndDate);
example_date is the date you're checking against, e.g. '2018-10-11'.
If you have DATETIME columns for start and end of a shift, you need to query for all shifts either starting within or before the current, or ending within or after the current month, e.g. to get shifts for August:
SELECT * FROM `shift`
WHERE `start` <= '2018-08-31'
AND `end` >= '2018-08-01';
The actual calculation of amount of days in the shift I'd do after the query.
Also make sure to use right datetime formats.