Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Last 6 Months

Tags:

sql

sql-server

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.

like image 847
DNac Avatar asked Oct 07 '13 14:10

DNac


People also ask

How do I get last 6 months data in SQL?

Instead of approximating the "current" date by selecting the MAX(date) the code could reference CAST(GETDATE() as DATE) to access the system datetime and cast it as type DATE. where [date] > dateadd(month, -6, cast(getdate() as date));

How do I get last 3 months records in SQL?

In SQL Server, you can use the DATEADD() function to get last 3 months (or n months) records.

How do I display 12 months in SQL?

SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month] FROM R when above sql execute how it is getting value for -N ??

How do I get last 30 days records in SQL?

SELECT * FROM product WHERE pdate >= DATEADD(day, -30, getdate()).


2 Answers

For MS SQL Server, you can use:

where datetime_column >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0) 
like image 64
codingbadger Avatar answered Sep 21 '22 17:09

codingbadger


In SQL Server

where datetime_column > dateadd(m, -6, getdate() - datepart(d, getdate()) + 1) 

SQLFiddle demo

In MySQL

where datetime_column > curdate() - interval (dayofmonth(curdate()) - 1) day - interval 6 month 

SQLFiddle demo

like image 35
juergen d Avatar answered Sep 20 '22 17:09

juergen d