How to get data before 6 months

Transact-SQL
1

I have a Dtm Field and the values 2016-04-27 14:08:00.000

How to retrieve all data before 6 months, or between 2 months

thanks

2

SELECT ...
FROM ...
WHERE YourColumn <= GETDATE()-182

I cheated, but you can also use DATEADD function.

1 Like
3

yes, I would assume it is >= GETDATE()-182

Also I used the following

my column >= Dateadd(Month, Datediff(Month, 0, DATEADD(M, -6, CURRENT_TIMESTAMP)), 0)
but it pulls up last 7 months data.

both ways should pull up the same number of data, where as it is not !

4

Those two expressions calculate different dates. Run these queries and you will see what I mean.

-- six months ago today
select dateadd(month, -6, getdate()); 

-- 182 days ago today
select dateadd(day, -182, getdate());

-- first of the month six months ago
select dateadd(month, datediff(month, 0, dateadd(month, -6, getdate())),0)
1 Like
5

If you want less one, adjust the calc on the GETDATE() value. I've simplified the calc to its bare essentials:

select dateadd(month, datediff(month, 0, getdate()) - 5, 0)
--or this, depending on the specific month(s) you want to see
--select dateadd(month, datediff(month, 0, getdate()) - 6, 0)

1 Like
6

What about if today is the last day of the month, are you happy that you get the same day in that month (even if the month six-months-ago has more days?

SELECT	DATEADD(Month, -6, '20160229') -- 29-08-2015 not 31-08-2015
SELECT	DATEADD(Month, -6, '20160831') -- 29-02-2016
SELECT	DATEADD(Month, -6, '20150831') -- 28-02-2015 - different depending on whether leap year, or not

I recommend you use the descriptive labels for date functions as we have found that mnemonics like "M" lead to confusion & errors in maintenance. Month, Minute, Millisecond, Microsecond?? D, DY, Y, DD, W and DW all relate to Day, Weekday, Day-of-Year - I don't think they are guessable nor obvious, "n" is minute, nanosecond uses something else, and so it goes on.

1 Like
7 
SELECT * 
FROM TABLE_NAME
WHERE Date_Column >= DATEADD(MONTH, -6, GETDATE())
1 Like
8

Made it much simpler . . Thank you every one . .. :slight_smile: