0

My Table with data. enter image description here

Now row 1,2,4 needed as output:

I try this:

SELECT * FROM my_table 
WHERE SUBSTRING_INDEX(my_table.`dates`, ',', 1) >= '01-01-2018'
AND SUBSTRING_INDEX(my_table.`dates`, ',', - 1) <= '13-02-2018'

But it is not working. Is there any good alternative solution? Please if any than suggest me or help me. I am stuck badly in this issue. Thanks

MJ Miraj
  • 115
  • 1
  • 12
  • 3
    The real solution would be to change the table schema and not store multiple values in a single column! – juergen d Dec 19 '18 at 11:37
  • Indeed, your data structure is not logical. The dates (and possible also the status on that date, it's not clear) should be in a second table wihch has the foreign key back to your main table to associate it. That's how proper relational databases are structured. Perhaps you should study database design concepts more thoroughly before you continue to work on your database. – ADyson Dec 19 '18 at 11:42
  • A now-disappeared comment linked to an excellent resource: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad the TLDR of which is "don't put CSV data in columns, or one day you may regret it". Today is probably that day for you and I strongly urge you to look into breaking this out as it should have been originally – Caius Jard Dec 19 '18 at 11:43
  • It is not possible.Because it was so many data and using index as a reference on so many table. @juergen-d – MJ Miraj Dec 19 '18 at 11:45
  • 1
    What you've written could work, but you'll need to convert your strings to dates to have it work properly. You can only string-compare dates like that if theyre in YYYYMMDD order, not DDMMYYYY – Caius Jard Dec 19 '18 at 11:46

1 Answers1

2

Firstly, you should really consider fixing the data; instead of storing them as comma separated values. Do Read: Is storing a delimited list in a database column really that bad?

Secondly, even with your current approach, you need to note that the MySQL Date format is YYYY-MM-DD, not DD-MM-YYYY (as in your stored values).

You will need to use Str_to_Date() function, in order to be able to do proper date comparison.

SELECT * FROM my_table 
WHERE STR_TO_DATE(SUBSTRING_INDEX(my_table.`dates`, ',', 1), '%d-%m-%Y') >= '2018-01-01'
AND STR_TO_DATE(SUBSTRING_INDEX(my_table.`dates`, ',', - 1), '%d-%m-%Y') <= '2018-02-13'
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57