-1

I'm amending a current query which I run on a fairly regular basis for a membership team looking at recent expiries. The clause in that query is:

and date_expiry between '2019-11-01' and '2019-12-31'

The dates are expanded to cover a 2 month period.

What I'd like to do is to create this query as an excel view in which they can refresh as an when they want. What I have so far and works to a degree* is the following:

and date_expiry between curdate()- interval 1 month and curdate()+ interval 3 month

However the issue many may have picked up on is that the above query gathers data from today 1 month previous (10/11/2019) and 3 months from today (10/02/2020).

So I've been searching around and the closest I've got was this:

and month(date_expiry) = month(current_date- interval 1 month ) and year(date_expiry)= year(curdate())

This works perfectly for collecting everything in the previous month (01/11/2019-31/11/2019) but I somehow need to add something similar to gather data data for the advanced months. Help please!

Punter
  • 31
  • 4
  • 1
    Tag your question with the database you are using. – Gordon Linoff Dec 10 '19 at 12:54
  • See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Dec 10 '19 at 14:50

2 Answers2

1

The curdate() suggests MySQL. You can handle full dates as:

where date_expiry >= (curdate() - interval (1 - day(curdate())) day) - interval 1 month and
      date_expiry < (curdate() - interval (1 - day(curdate())) day) + interval 1 month

This is convenient because it is index-friendly.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks very much for this. However it's not querying as it's suggesting that the clause is not closed due to the amount of brakets. Playing around as we speak. – Punter Dec 10 '19 at 13:10
0

Try DATE_SUB for substraction and DATE_ADD for 2 months advanced cek this query is this the day you want to cek? you can change the interval if you want and change the NOW() with your custom date yourself. to learn about interval check this link

SELECT DATE_SUB(DATE(CONCAT_WS('-', YEAR(NOW()) , MONTH(NOW()), 31)),INTERVAL 1 MONTH) AS lastdaymonthbefore, 
DATE_ADD(DATE(NOW()),INTERVAL 2 MONTH) AS 2monthAdvanceFromToday

so you can edit your query like this

AND date_expiry BETWEEN DATE_SUB(DATE(CONCAT_WS('-', YEAR(NOW()) , MONTH(NOW()), 31)),INTERVAL 1 MONTH) AND DATE_ADD(DATE(NOW()),INTERVAL 2 MONTH)
ahmed
  • 9,071
  • 3
  • 9
  • 22
Asep Sonjaya
  • 1
  • 2