Mysql: Select all data between two dates

Question

You can use a concept that is frequently referred to as 'calendar tables'. Here is a good guide on how to create calendar tables in MySql:

-- create some infrastructure
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- only works for 100 days, add more ints joins for more
SELECT cal.date, tbl.data
FROM (
    SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date
    FROM ints a JOIN ints b
    ORDER BY a.i * 10 + b.i
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

You might want to create table cal instead of the subselect.

Select *  from  emp where joindate between date1 and date2;

But this query not show proper data.

Eg

1-jan-2013 to 12-jan-2013.

But it's show data

1-jan-2013 to 11-jan-2013.

its very easy to handle this situation

You can use BETWEEN CLAUSE in combination with date_sub( now( ) , INTERVAL 30 DAY ) AND NOW( )

SELECT
    sc_cust_design.design_id as id,
    sc_cust_design.main_image,
    FROM
    sc_cust_design
WHERE
    sc_cust_design.publish = 1 
    AND **`datein`BETWEEN date_sub( now( ) , INTERVAL 30 DAY ) AND NOW( )**

Happy Coding :)

Do you have a table that has all dates? If not, you might want to consider implementing a calendar table and left joining your data onto the calendar table.