Retrieve records from one month ago today?

1

Dear all,

Is there a way I can retrieve records from a table from exactly one month ago from today's date? The way I am doing it at present is as below:

SELECT * FROM table1 WHERE DateEntered> GETDATE()-31

However there are not always 31 days in a month so it is not always entirely accurate.

2 
SELECT * FROM table1 WHERE DateEntered > DATEADD(mm,-1,GETDATE());

Test to make sure that when you have dates such as Feb 28 or 29 in the mix, what you are getting from the query is as per your requirements.

Also, if your DateEntered has a time portion to it, what should be the behavior? Anything greater than the exact time one month ago, or anything starting the following day?

1 Like
3

Thanks James, this is a much better way of doing it.

There is a time portion to it (EG 2015-06-22 08:33:44.620), ideally I only want to include results relevent to the previous month.

4

I didn't quite follow what you mean. Assume that you are running the query now, when GETDATE() returns 2015-07-20 10:57:47.410.

Do you want to return records that have DateEntered greater than ( or perhaps greater than or equal to)
a) midnight of 2015-06-21 or,
b) midnight of 2015-06-20 or,
c) 2015-06-20 10:57:47.410 or,
d) midnight of 2015-07-01

1 Like
5

SELECT * FROM table1 WHERE DateEntered > CONVERT(char(8), DATEADD(MONTH, -1, GETDATE()), 112)

1 Like
6

Hi James, D midnight would be the most suitable.

Thanks for the second approach Scott!

7

So you really want only the data for the current month regardless of what day of the month it is (and not for the prior one month period). If that is the case,

SELECT * FROM table1 
WHERE DateEntered >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0);
1 Like
8

Yes that's it exactly :). Thanks again!