CODEWITHSUNDEEP
mysql
user663724
user663724

Reputation:

why DATE_ADD and str_to_date is not returning any results?

From User Interface , user can select a date and click on the submit button , based on the input field how to display next 30 days records ??

This is my sqlfiddle http://sqlfiddle.com/#!9/dbcd9/1

i have tried using DATE_ADD and str_to_date 

select * from historical_data where current_day between 
str_to_date('2015-10-02','%Y-%m-%d') and DATE_ADD(str_to_date('2015-10-02','%Y-%m-%d'),
INTERVAL 1 MONTH)

But why it is not displaying any data ??

Upvotes: 0

Views: 109

Answers (2)

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

use str_to_date like that :-

str_to_date('01-OCT-2015','%d-%b-%Y')

or 

str_to_date(current_day,'%d-%b-%Y')

use str_to_date on where case :-

where str_to_date(current_day,'%d-%b-%Y')

query :-

where str_to_date(current_day,'%d-%b-%Y') between 
'2015-10-02' and DATE_ADD('2015-10-02',INTERVAL 1 MONTH);

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to use str_to_date on on the column name which is saved as varchar dates 

select * from historical_data 
where str_to_date(current_day,'%m-%b-%Y') 
between 
'2015-10-02' and DATE_ADD('2015-10-02',INTERVAL 1 MONTH);

Upvotes: 1

Related Questions