CODEWITHSUNDEEP
mysqldatabasebetween
codeinprogress
codeinprogress

Reputation: 3501

MySQL getting records between specific month, year

I have a table called Accounts which has a date column named TransactionDate. I need to get all the records where TransactionDate is between month,year.

For example, all records where TransactionDate is between 6, 2016 and 8, 2017.

My query string looks like:

SELECT Amount 
  FROM Accounts 
 WHERE (MONTH(TransactionDate) BETWEEN 6 and 8) 
   AND (YEAR(TransactionDate) BETWEEN 2016 and 2017)  
 ORDER BY TransactionDate ASC

However it only gives me records for June, July 2016 and August 2017. It skips the records for Jan 2017, Spet, Nov 2016.

I am attaching the screenshot of the TransactionDate column of all records (there are 31 of them)

enter image description here

Here is the screenshot of the returned result

enter image description here

What is wrong with my query? Also this query is going to be dynamic, i.e. users will choose the month and year from a form and the result will be returned to them.

Thank you.

Upvotes: 0

Views: 1030

Answers (5)

Jay Momaya
Jay Momaya

Reputation: 2049

You can do this easily by using date_format function of mysql

 SELECT 
     Amount
 FROM
     Accounts
 WHERE
     DATE_FORMAT(TransactionDate, '%Y-%m') BETWEEN '2016-06' AND '2017-08' ORDER BY TransactionDate ASC;

This should work perfectly.

Upvotes: 0

Aidan Connelly
Aidan Connelly

Reputation: 184

You simply filter the queries by those that are BEFORE 2017-08 and AFTER 2016-06

SELECT Amount 
  FROM Accounts 
 WHERE TransactionDate > '2016-06-01T00:00:00' 
   AND TransactionDate < '2017-08-01T00:00:00' 
 ORDER BY TransactionDate ASC

what's wrong with your current query is that you're getting data where the month is June, July, or August, AND where the year is 2016 or 2017. This isn't right, you want to filter by the Date itself, not it's components.

Upvotes: 1

Ali Faris
Ali Faris

Reputation: 18592

I think this what you need

SELECT Amount , ExpenseType , 
      MONTHNAME(TransactionDate) AS TransactionMonthString
      Year(TransactionDate) AS TransactionYearString
FROM Accounts 
WHERE TransactionDate >= '2016-06-01' AND TransactionDate <= '2017-08-01'   
ORDER BY TransactionDate

Upvotes: 0

Jok3r
Jok3r

Reputation: 496

select Amount from Accounts where TransactionDate  BETWEEN '2016-04-02' AND '2017-08-03';

try this,good luck

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You would seem to want:

select a.Amount
from Accounts a
where a.TransactionDate >= '2016-06-01' AND
      a.TransactionDate < '2017-09-01'
order by a.TransactionDate ASC

Upvotes: 0

Related Questions