This might help you I have a query that involves searching database over a range of 30 days. Queries, both with correct output and wrong output are below: , I solved it myself using a totally different method.
Find if given date range inside another date range in MySQL
it fixes the issue It sounds like you want any promotional sale that is active during the range. In that case, you don't need to check that the start dates and end dates exist in the range, which is what you are doing now. You simply need to check that the start date happened before the range is closed, and then that the end date happened after the range opened. Hope that makes sense?
WHERE isActive = 1
AND start_date < '2014-12-13 11:45:00'
AND end_date > '2014-12-11 15:45:00';
How to compare current date with a date range or discrete date values in MySQL?
To fix the issue you can do I would definitely use find_in_set() and substring_index(), if I had such a lousy data structure:
where find_in_set(date_format(curdate(), '%Y-%m-%d'), datecol) > 0 or
(datecol like '%,%' and datecol not like '%,%,%' and
date_format(curdate(), '%Y-%m-%d') between substring_index(datecol, ',', 1) and substring_index(datecol, ',', -1)
Laravel - PHP/MySQL date range filter against date range in database
With these it helps I am working on an application using Laravel and I am trying to filter records from the database. Here is the criteria: , Your initial excluded_period can't be between your start and end:
wish of those help I find the answer for my needs. I will post below maybe will help another people. It work great.
tableCalculated.timeACalculated AS timeA,
tableCalculated.timeBCalculated AS timeB,
TIMEDIFF(tableCalculated.timeBCalculated, tableCalculated.timeACalculated) AS period
WHEN TIMEDIFF(timeA, '$zistart') < 0 THEN '$zistart'
WHEN TIMEDIFF(timeA, '$zistart') > 0 THEN timeA END) AS timeACalculated,
WHEN TIMEDIFF(timeB,'$ziend') < 0 THEN timeB
WHEN TIMEDIFF(timeB,'$ziend') > 0 THEN '$ziend' END) AS timeBCalculated
imei = '$imei'
timeBCalculated <= '$ziend' AND timeACalculated >= '$zistart' ORDER BY id ASC
) AS tableCalculated
HAVING period > 0";