# How to get data before 6 months

#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.

#3

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

Also I used the following

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

-- 182 days ago today

-- first of the month six months ago

#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)

#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.

#7
``````SELECT *
FROM TABLE_NAME
WHERE Date_Column >= DATEADD(MONTH, -6, GETDATE())``````

#8

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