How to compare current date with a date range or discrete date values in MySQL?

Question

I am saving multiple dates in database in one column separates by comma(,) like

2017-03-05, 2017-03-06, 2017-03-07, 2017-03-08, 2017-03-08.
2017-03-05, 2017-03-08

I just want to know that how I compare current date by these dates. In the first example, its easy, but problem is for the second one.

Can anyone help me out....?

Thanks...

Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 — e4c5, Mar 07 '17 at 04:48
This is not how you should be storing your data. Normalize the table. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad I'm also not clear how example 1 is easy but 2 is not.. — chris85, Mar 07 '17 at 04:49
These are batch dates. there are two types batches 1. Weekend Batches (example 1) 2. Daily batches (example 2) Now I want to create Attendance Module, for that I want to compare current date with these dates. So in first example I can directly compare by using LIKE, but not for second. Is there any SQL query for compare current date by these dates? — GYaN, Mar 07 '17 at 05:00
I want something like this 1. 2017-03-05,2017-03-06,2017-03-07,2017-03-08,2017-03-08. 2. 2017-03-05,2017-03-08 CURRENT_DATE >= 2017-03-05 AND CURRENT_DATE<=2017-03-08 IN SQL — GYaN, Mar 07 '17 at 05:17
So as I understand it there are two cases: If there are exactly two dates, the current date should be tested to see if it falls between the range defined by those dates. Otherwise, the current date should be compared for an exact match against any of the individual dates. Is this correct? — Steve Chambers, Jul 06 '17 at 08:21
Next you will want a mixture of discrete dates and date ranges? — Rick James, Jul 07 '17 at 00:21

score 4 · Answer 1 · answered Jul 06 '17 at 08:53

As others have commented, it would be preferable to normalise the data and store individual dates in multiple rows of a linked table rather than in a single field. But assuming there's a valid reason this can't be done, the following will do what you've asked:

SELECT date_csv,
       CASE WHEN CHAR_LENGTH(REPLACE(date_csv, ',', '')) = CHAR_LENGTH(date_csv) - 1
            THEN /* date_csv has a single comma */
                 CURRENT_DATE >= SUBSTRING(date_csv, 1, INSTR(date_csv, ',') - 1)
                 AND CURRENT_DATE <= SUBSTRING(date_csv, INSTR(date_csv, ',') + 1)
            ELSE /* date_csv has either no comma or multiple commas */
                 FIND_IN_SET(DATE_FORMAT(CURRENT_DATE,'%Y-%m-%d'), date_csv) > 0
       END AS 'Test result'

Rextester demo: http://rextester.com/EPKO65812

On board with your answer! – Harish Rawat Jul 07 '17 at 07:00 — Harish Rawat, Jul 07 '17 at 07:00

sumit · Answer 2 · 2017-03-07T05:18:07.457

2

you can use FIND_IN_SET to compare CURRENT_DATE with your comma separated dates

SELECT FIND_IN_SET(CURRENT_DATE,'2017-03-05,2017-03-06,2017-03-07,2017-03-08,2017-03-08');

EDIT

MySQL FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings, so for your second set it simple returns zero

so you can do some thing like below

 SELECT * from tbl where FIND_IN_SET(CURRENT_DATE,'2017-03-05,2017-03-08')>0

edited Mar 07 '17 at 05:18

answered Mar 07 '17 at 05:05

sumit

15,003
12
69
110

I can use the for first example, What for second. – GYaN Mar 07 '17 at 05:07
so are you talking about missing data? – sumit Mar 07 '17 at 05:10
Not missing data first is weekend batch (date of every days). Second is daily batch (only first and last date) – GYaN Mar 07 '17 at 05:36
2

@GyandeepSharma Don't do this. Instead, fix your broken schema. – Strawberry Mar 07 '17 at 07:55

score 2 · Answer 3 · answered Mar 07 '17 at 05:13

2

Use FIND_IN_SET

 SELECT * FROM your_table
 WHERE FIND_IN_SET('2017-03-08',comma_date );

answered Mar 07 '17 at 05:13

Rafiqul Islam

1,636
1
12
25

score 0 · Accepted Answer · answered Jul 08 '17 at 03:00

I would definitely use find_in_set() and substring_index(), if I had such a lousy data structure:

select t.*
from t
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)
      );

Komal Bandi · Answer 5 · 2017-03-07T05:11:37.783

-1

<?php

$string_date='2017-03-05,2017-03-06,2017-03-07,2017-03-08,2017-03-08';
$tmp_dates=explode(',',$string_date);

foreach($tmp_dates as $tmp_date)
{
$newDateString = date_format(date_create_from_format('Y-m-d', $tmp_date), 'Y-m-d');

$interval =date_diff(date_create($newDateString),date_create(date('Y-m-d')));
echo $interval->format('%R%a days');
echo '<br>';
}
?>

edited Mar 07 '17 at 05:11

answered Mar 07 '17 at 05:09

Komal Bandi

67
1
7

How to compare current date with a date range or discrete date values in MySQL?

5 Answers5