# Thread: Date calculations

1. Registered User
Join Date
Nov 2004
Posts
35

## Unanswered: Date calculations

Hi guys,

I know how to do this in a long-winded way, but was hopeful for some tricks from old pros.

Consider a table with { id, date }; these are auto-increment integer and DATETIME columns respectively.

How do I generate a query that returns all 'id's associated to date elements that have values not in this month, but in the previous X months?

Ex, data:

1, 2008-02-01
2, 2008-02-30
3, 2008-03-03
4, 2007-11-01

If I want the query for the previous two months, it should return ids:
1,2

DATE_SUB( INTERVAL, X MONTH ) won't do the trick (alone), because we don't want ranges in two months previous to today, but instead, all entries contained in Jan, Feb, (since we're now in March).

Thanks as always.

2. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
first calculation: find the first day of the current month

second calculation: subtract two months

then in the WHERE clause, choose all dates which are greater than or equal to the second date, and less than the first

simple, yes?

3. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
by the way, only mysql will allow the date for id=2, and only in earlier versions

4. Registered User
Join Date
Mar 2008
Location
Tacoma, WA
Posts
11
If you are still looking for more details, hope this will help.

Code:
```select * from {table}
where date between
date_sub(
date_sub(current_date, interval day(current_date)-1 day),
interval 2 month)
and date_sub(
date_sub(current_date,
interval day(current_date)-1 day),
interval 1 month)```

5. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
it helps, xtremenw, but it's slightly off in two ways

let's use march as an example

firstly, the bounds you have created are the first day of january and the first day of february

this is not the two month span that was originally asked for, but your formula can be changed as required

the second problem is that you include the upper bound, because you're using BETWEEN, and that's not right

6. Registered User
Join Date
Mar 2008
Location
Tacoma, WA
Posts
11
Originally Posted by r937
it helps, xtremenw, but it's slightly off in two ways

let's use march as an example

firstly, the bounds you have created are the first day of january and the first day of february

this is not the two month span that was originally asked for, but your formula can be changed as required

the second problem is that you include the upper bound, because you're using BETWEEN, and that's not right

Code:
```select * from {table}
where date_stamp between
date_sub(
date_sub(current_date, interval day(current_date)-1 day),
interval 3 month)
and date_sub(
date_sub(current_date,
interval day(current_date)-1 day),
interval 1 month) - interval 1 day```
Good call! It was late and I really did not test it, I just got it to work.

7. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
now you've changed it so that, assuming you run it this month (march), it would return rows from december 1 to midnight january 31st

not exactly the previous two months (which would be january and february)

also, note that all datetimes for the remainder of january 31st after that tiny little second at exactly midnight would not be included

you simply must stop using BETWEEN and start thinking of greater than or equal to for the lower bound, but strictly less than for the upper bound

8. Registered User
Join Date
Mar 2008
Location
Tacoma, WA
Posts
11

I figured out what I was doing. I was trying things out against a date type and not datetime. He should get the general idea and be able to take it from there.
Last edited by xtremenw; 03-07-08 at 23:43.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•