MySQL, the data of the broken column and get the result with a breakdown of data

Question

I have MySQL table data that look below

+--------------------------+ | period | +--------------------------+ | 2014-11-27 to 2014-11-28 | | 2014-11-09 to 2014-11-09 | | 2014-11-07 to 2014-11-07 | | 2014-11-06 to 2014-11-06 | | 2014-11-04 to 2014-11-04 | | 2014-11-02 to 2014-11-02 | | 2014-11-01 to 2014-11-02 | | 2014-10-24 to 2014-10-24 | | 2014-10-23 to 2014-10-24 | | 2014-10-23 to 2014-10-24 | | 2014-10-23 to 2014-10-23 | | 2014-10-23 to 2014-10-23 | | 2014-10-20 to 2014-10-20 | | 2014-10-18 to 2014-10-18 | | 2014-10-17 to 2014-10-17 | | 2014-10-13 to 2014-10-13 | | 2014-10-13 to 2014-10-13 | | 2014-10-09 to 2014-10-10 | | 2014-10-06 to 2014-10-07 | | 2014-10-01 to 2014-10-10 | | 2014-09-30 to 2014-09-30 | | 2014-09-24 to 2014-09-24 | | 2014-09-12 to 2014-09-12 | | 2014-09-12 to 2014-09-12 | | 2014-09-08 to 2014-09-09 | | 2014-09-08 to 2014-09-08 | | 2014-09-08 to 2014-09-08 | | 2014-09-01 to 2014-09-01 | | 2014-09-01 to 2014-09-01 | +--------------------------+ 

Here I would like information between two dates of the type ( 2014-09-01 to 2014-09-30 ) or ( 2014-09-01 to 2014-010-3 0) or ( 2014-09-01 to 2014-11-30 ), and the varchar column, is there any way to get results between two dates?

Answer 1

I think below is correct

 select period from mysql_common.leave_details where (period like '%-11-%' or period like '%-10-%' or period like '%-09-%') order by period desc; 

Answer 2

In any case, you should normalize your table and save the dates using mysql date and time data types and make sure you save them in the two columns start_date and end_date . It will make your life easier.

Now, returning to the current situation, you can do this, first extract the start and end date from the varchar string using the substring_index function and finally use them in the condition using the having .

 select str_to_date(substring_index(period,'to',1),'%Y-%m-%d') as start_date, str_to_date(substring_index(period,'to',-1),'%Y-%m-%d') as end_date, period from table_name having start_date >='2014-09-01' and end_date <='2014-09-30'; 

Answer 3

Try the following:

 SELECT * FROM table1 A WHERE DATE(LEFT(A.period, 10)) BETWEEN DATE('2014-09-01') AND DATE('2014-09-30') AND DATE(RIGHT(A.period, 10)) BETWEEN DATE('2014-09-01') AND DATE('2014-09-30');