CODEWITHSUNDEEP
mysqlsql
Sajjad
Sajjad

Reputation: 893

Find data of a whole month in sql

I have this query where I provide to-date & from date.

SELECT * 
FROM sales 
WHERE date between to-date AND from-date;

Now I want to execute this query with following parameters

to-date = Oct-2015

some-other-date = Oct-2015

That is I want records of the whole month.

How would I do that in a query where I have to and from dates provided it will work for both scenarios where months can be same and different as well.

Update:

dataType for column date is date

Upvotes: 0

Views: 2147

Answers (2)

Khurram Ali
Khurram Ali

Reputation: 1679

select * from sales 
where from-date >= 1-Oct-2015 
and to-date <= 1-Nov-2015

Update 

 select * from sales 
where date >= from-date 
and date <= to-date

Here is SQLFIDDLE

You Can get month from your both to and from dates and find records of that month 

SELECT * FROM sales
WHERE MONTH('2002-01-03') AND MONTH('2002-01-3')

SqlFiddle of Using Month Function

Upvotes: 0

O. Jones
O. Jones

Reputation: 108651

You can find the first day of the month containing any given timestamp with an expression like this. For example by using the timestamp NOW(), this finds the first day of the present month.

(DATE(NOW() - INTERVAL DAYOFMONTH(DATE(NOW()))

That's handy, because then you can use an expression like 

(DATE(NOW() - INTERVAL DAYOFMONTH(DATE(NOW())) - INTERVAL 1 MONTH

to find the beginning of the previous month if you like. All sorts of date arithmetic become available.

Therefore, you can use an expression like the following to find all records with item_date in the month before the present month.

WHERE item_date>=(DATE(NOW()-INTERVAL DAYOFMONTH(DATE(NOW()))- INTERVAL 1 MONTH
  AND item_date < (DATE(NOW()-INTERVAL DAYOFMONTH(DATE(NOW()))

Notice that we cast the end of a range of time as an inequality (<) to the moment just after then end of the range of time.

You may find this writeup useful. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

It's often useful to create a stored function called TRUNC_MONTH() to perform the conversion of the arbitrary timestamp to the first day of the month. It makes your SQL statements easier to read.

Upvotes: 1

Related Questions