SQL date problem

Databases
1

I have a table of member information which includes an expiration date for each member.

I need a result set that includes everyone who will expire 2 months from now… however it isn’t exactly “2 months from today”. Instead, I need it to determine the month that is 2 months from now, then include every member who will expire at some time within that month.

For example, today is April 18, so I would need a query that says “give me ever member that expires in June”. If I ran the query tomorrow it would still get every member expiring in June.

When I run it again - some time mid May - it should get every member expiring in July.

My php-programming brain wants to say "get the month number of today (4), add 2 (4+2), then match every expiration date that begins with month 6. But I don’t know how to do this in SQL.

Any suggestions?

2

Someone’s gonna do this better than me, but…

SELECT * FROM table 
WHERE date > CONCAT(YEAR(CURDATE()),"-",MONTH(CURDATE())+2,"-00") 
AND date < CONCAT(YEAR(CURDATE()),"-",MONTH(CURDATE())+3,"-00")
3 
SELECT
    *
FROM
    table
WHERE
    date > NOW() + INTERVAL 2 MONTH
4

Wouldnt that just shove today’s date up 2 months, Space?

His idea was to get everyone from June, not everyone after June 18.

5

i’m gonna give you the long explanation first

when doing any type of date search, it’s important to remember that you’ll get a table scan unless you take steps to ensure that the index on the date column can be used

this means that you want a query that looks similar to starlion’s –

WHERE expirydate >= ...

except that the rest of starlion’s code is broken (month+3 in december will give month 15, right?)

another thing to remember is that it’s much better to set an open upper end to a date range

so for example if you want all dates in a month, this type of construction is problematic –


WHERE expirydate >= '2011-06-01'
  AND expirydate <= '2011-06-30'

the reason is, of course, that you would have to figure out the last day of the month, which gets messy for february

furthermore, if your column had datetimes instead of dates, you’d end up doing something like this –


WHERE expirydate >= '2011-06-01 00:00:00'
  AND expirydate <= '2011-06-30 23:59:59'

which is even messier (and can fail if your database system supports fractions of a second)

with an open upper end of the range, you would write –


WHERE expirydate >= '2011-06-01'
  AND expirydate  < '2011-07-01'

the neat part about this solution is that

  1. it allows the index to be used, i.e. it is optimized and efficient, and
  2. no “last day of month” calculation is required, and
  3. it covers datetimes as well as dates

so with that out of the way, what we need to do for this particular situation is find the first day of the month for the month 2 months ahead of this month, and the first day of the month for the month after that

with me so far? :slight_smile:

6 
WHERE expirydate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                 + INTERVAL 2 MONTH
  AND expirydate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                 + INTERVAL 3 MONTH

the only tricky part here is DAYOFMONTH(CURRENT_DATE) - 1

today’s the 18th, so this equates to 17, and if you subtract 17 days from today, you get April 1st

if you happen to do this on the 1st of any month, then DAYOFMONTH(CURRENT_DATE) gives 1, and you subtract 1 less than that, namely 0, and thus still end up with the 1st of the month as your answer

the rest of the formula should be obvious

:cool:

7

completely spaced out the +2/+3 looping around… lol. Still, you could use…


SELECT * FROM table 
WHERE DATE > CONCAT(YEAR(CURDATE() + INTERVAL 2 MONTH),"-",MONTH(CURDATE() + INTERVAL 2 MONTH),"-00") 
AND DATE < CONCAT(YEAR(CURDATE() + INTERVAL 3 MONTH),"-",MONTH(CURDATE() + INTERVAL 3 MONTH),"-00")

… right?

8

not really :slight_smile:

i mean, it might work, but only if you factor in the fact that string manipulation is A LOT slower than date arithmetic, and that you’re playing with invalid dates there…

:smiley:

9

I just ran a test on this query, and running it with ‘2011-06-01’ in place of CURRENT_DATE returned a result from 2011-05-31… are you SURE that -1 on day 1 = day 1, and not last month’s last day?

10

DAYOFMONTH(CURRENT_DATE) is 18, i.e. an integer, so you can’t just stuff ‘2011-06-01’ in there

11

Thanks so much for all the help. I’m getting CONCAT is not a function and also NOW is not a function.

r937 - while I get the logic of all your explanations, I don’t know what the INTERVAL bit is doing… is this a SQL function? I’m not so good with SQL beyond a select statement. I’m having trouble understanding how the “+ INTERVAL 2 MONTH” works at the end.

12

yeah, sorry, eric, i forgot that this wasn’t the mysql forum

would you mind letting us know which database system you’re using?

13

if it’s SQL Server, you want this –

WHERE expirydate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())+2,0)
  AND expirydate  < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+3,0)

GETDATE() returns the current datetime, not date

the DATEDIFF function counts the number of month boundaries between the current datetime and the “zero date” – in effect, it strips the time off, as well as ignoring the day of month

then DATEADD adds back the number of month boundaries

DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) gives the first day of the current month

DATEADD(mm,DATEDIFF(mm,0,GETDATE())+2,0) gives the first day of the month in 2 months

14

SQL Server 2005 - sorry I forgot to mention that!

15

Initial testing is working well with this…

16

Out of curiousity, r937 - if it works correctly on day 1 (1 - 1 = 0), why have the -1 day at all? Since the 0’th of the month = 1st of the month?

17

oh, really?

18

Hmm… okay, I think i see where i’m going wrong…
When you say


CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                 + INTERVAL 2 MONTH

It gets translated as

CURRENT_DATE 
- (INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY)
+ INTERVAL 2 MONTH

Because in my head, it went…

CURRENT_DATE //04-01
- INTERVAL DAYOFMONTH(CURRENT_DATE) //04-00?
- 1 DAY //Now were at the end of March
+ INTERVAL 2 MONTH // should be the end of May?

Silly implied order of operations.

19

Rudy and everyone, thanks for the help with this.