Mysql date range query only check date without check month and year

0
votes

I want in select records based on date condition of day only without checking month and year.

Table structure 
------------------------------------
id | user_id  | amount | created_on
------------------------------------

If I select date 15-April to 25-April, Then I need record between 15-April to 25-April, 15-March to 25-March and so on..

It seems quite easy with fetch day from date and compare it with 

Select sum(amount) from sales where DATEFORMAT(created_on, %d) >= 15 and DATEFORMAT(created_on, %d) <=25

But how to find records when i select date 28-March to 5-April, then I need data of 28-February to 5-March, 28-January to 5-February.. and so on

4
phpmysqldatedate-range
You cannot check it without month in this case :) – Goikiu
@Goikiu what about data of 28 February, 28 January and so on? – Haresh Vidja
I think that you cannot achieve this without using the full date. Using the full date will however be different from what you asked ;) – Goikiu
See meta.stackoverflow.com/questions/333952/… – Strawberry

4 Answers

1
votes

If you are using php with it. you can compare minimum and maximum dates and change your query like this:

Attempt #1:

    $min_date = 15;
    $max_date = 25;

Attempt #2:

    $min_date = 28;
    $max_date = 5;

Script:

    if($min_date < $max_date)
    {
        $query = "Select sum(amount) from sales where DATEFORMAT(created_on, %d) >= $min_date and DATEFORMAT(created_on, %d) <=$max_date";    
    }
    else
    {
        $query = "Select sum(amount) from sales where DATEFORMAT(created_on, %d) >= $min_date OR DATEFORMAT(created_on, %d) <=$max_date"; 
    }
0
votes

You can use SQL BETWEEN Operator

Select sum(amount) from sales where created_on BETWEEN '2017/03/28'  and '2017/04/05'

Source SQL Between Operator

0
votes

Can you try this? (X1 and X2 are your values)

Select sum(amount) 
from sales 
where 
    (1= CASE WHEN X2>=X1 THEN 1 ELSE 0 END AND DATEFORMAT(created_on, %d) >= X1 and DATEFORMAT(created_on, %d) <=x2)
OR (1= CASE WHEN X2<X1 THEN 1 ELSE 0 END AND ( DATEFORMAT(created_on, %d) >=X1 OR DATEFORMAT(created_on, %d) <=X2))
-1
votes

use this to extract only date from your timestamp and then compare it with ur requirement.

select SUBSTRING(date(CURRENT_TIMESTAMP), 9, 2) , SUBSTRING(date('2017-04-27' ), 9, 2)

OUTPUT: 27 , 27

Cheers!!