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 gt 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

For MS SQL Server you can use where datetime column gt Dateadd Month Datediff Month 0 DATEADD m -6 current timestamp 0

SQL Last 6 Months - SyntaxFix

For MS SQL Server, you can use:

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

~ Answered on 2013-10-07 15:08:58