1
votes

I have an embedded query that I use to pull previous month data. Everything has been working fine until this month (January). My code looks like this:

(MONTH(CURRENT DATE)-1) = MONTH(TSTAMP)

I have it setup this way because I have a timestamp in my data that I base the query off of. This usually works like a charm, but it's not working this month and I think it's because of the new year. How does this function work when dealing with a different year? Is there a way to write it into the query so I don't have to worry about a change in year?

2

2 Answers

1
votes

You can do this by using the year, like this:

YEAR(CURRENT DATE) * 12 + MONTH(CURRENT DATE) - 1 = YEAR(TSTAMP) * 12 + MONTH(TSTAMP)

This, in essence, converts the dates into months since time 0 -- so the -1 makes sense.

0
votes

The proper way to do this is with a range query (one with an exclusive upper-bound, <, too), so that the db is free to us an index if one is available.
The first of the month can be retrieved pretty easily via:

CURRENT_DATE - (DAY(CURRENT_DATE) - 1) DAYS

(Subtract the difference in days between the current date and the start of the month)

This gives a wonderful upper-bound condition for the query:

WHERE tStamp < CURRENT_DATE - (DAY(CURRENT_DATE) - 1) DAYS

(Get everything before the start of the current month).

However, since we're really only interested in the previous month, we also need to limit the lower bound. Well that's everything since, or on, the start of that month... and since we can already get the start of the current month:

WHERE tStamp >= CURRENT_DATE - (DAY(CURRENT_DATE) - 1) DAYS + 1 MONTH
      AND tStamp < CURRENT_DATE - (DAY(CURRENT_DATE) - 1) DAYS

calendarDate - DATE
year         - INTEGER
month        - INTEGER
dayOfMonth   - INTEGER

... you can use this table to get the relevant values instead:

WHERE tStamp >= (SELECT calendarDate
                 FROM calendarTable
                 WHERE year = YEAR(CURRENT_DATE - 1 MONTH)
                       AND month = MONTH(CURRENT_DATE - 1 MONTH)
                       AND dayOfMonth = 1)
      AND tStamp < (SELECT calendarDate
                    FROM calendarTable
                    WHERE year = YEAR(CURRENT_DATE)
                          AND month = MONTH(CURRENT_DATE)
                          AND dayOfMonth = 1)

(there's a couple of different forms of this, but this one looks pretty simple)