sql server - SQL Last 6 Months

Question

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.

question from:https://stackoverflow.com/questions/19227964/sql-last-6-months

Answer 1

For MS SQL Server, you can use:

where datetime_column >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6,
current_timestamp)), 0)