I have table containing one datetime column. I need to return rows for only last 6 months. This can be done by
where datetime_column > DATEADD(m, -6, current_timestamp)
But how to extend this option if I want to return latest month beginning with first day of the month? E.g. I run this condition in the middle of month (14/6/2000), the latest row is set to 14/1/2000, but i would like to return it as 1/1/2000. Any advice?
I tried some subqueries (max function of datetime including month function) but with no success.
This question is related to
sql
sql-server
For MS SQL Server, you can use:
where datetime_column >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6,
current_timestamp)), 0)
where datetime_column > curdate() - interval (dayofmonth(curdate()) - 1) day - interval 6 month
where datetime_column > dateadd(m, -6, getdate() - datepart(d, getdate()) + 1)
select *
from tbl1
where
datetime_column >=
DATEADD(m, -6, convert(date, convert(varchar(6), getdate(),112) + '01'))
.... where yourdate_column > DATE_SUB(now(), INTERVAL 6 MONTH)
Try this one
where datediff(month, datetime_column, getdate()) <= 6
To exclude or filter out future dates
where datediff(month, datetime_column, getdate()) between 0 and 6
This part datediff(month, datetime_column, getdate()) will get the month difference in number of current date and Datetime_Column and will return Rows like:
Result
1
2
3
4
5
6
7
8
9
10
This is Our final condition to get last 6 months data
where result <= 6
Source: Stackoverflow.com