CODEWITHSUNDEEP
mysqlsqldateselectbetween
user4246994
user4246994

Reputation:

MySQL, split column data and get result by splitted data

I have a MySQL table data that looks like 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 to get the details between two dates like (2014-09-01 to 2014-09-30) or (2014-09-01 to 2014-010-30) or (2014-09-01 to 2014-11-30) and it's a varchar column, is there any possibilities to get results between two dates?

Upvotes: 5

Views: 91

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

FIrst thing you should really normalize your table and store the dates with mysql native date data-types and make sure you store them in two columns start_date and end_date. This will make your life easy.

Now coming back to the current situation there is a way you can do it, first extract the start and end date from the varchar string using substring_index function and finally use them in the condition using having clause.

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';

Upvotes: 1

user4246994
user4246994

Reputation:

i think below is right one,

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

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

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');

Upvotes: 0

Related Questions