Sir I have this variable

$date="2016-07-18"

I want to get DATA from 2016-07-01 to 2016-07-17.
In other words I want to get DATA of month given in $date (July) but excluding current date (18)

How to write query

$query = "select * from arrival where ........;

Please help

You can do:

SET @dt = '2016-07-18';
SELECT * FROM `arrival` WHERE `created_at` >= DATE_FORMAT(@dt ,'%Y-%m-01') AND `created_at` < @dt;

the DATE_FORMAT() will output the first day of the month 2016-07-01, since you don't want to include the last day you cannot use the BETWEEN ... AND statement.

More info:

sir i used this line

$query = "SELECT * FROM `arrival` WHERE `created_at` >= DATE_FORMAT('".$date."' ,'%Y-%m-01') AND `created_at` < '".$date."';";
echo $query;

it says:
SELECT * FROM arrival WHERE created_at >= DATE_FORMAT('2016-07-18' ,'%Y-%m-01') AND created_at < '2016-07-18';ErrorUnknown column 'created_at' in 'where clause'

How to overcome this

ErrorUnknown column 'created_at' in 'where clause'
How to overcome this

Replace with the appropriate columns, mine is an example, I don't know your table structure.

commented: I'll upvote and share my table has four legs. +10
commented: I'll upvote also coz Sir rproffitt has the best structure of table lol +2
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.