How to get only a particular month's data in SQL

Question

I am trying to get only previous sixth month's data form the query.

i.e I have to group by only the previous sixth months.

Suppose current month is June then I only want January's data & also I don't want all the previous month other than January

Can anyone help me for this

SELECT 
   so_date
FROM 
   RS_Sells_Invoice_Info_Master SIIM
LEFT OUTER JOIN 
   RS_Sell_Order_Master AS SM ON SM.sell_order_no = SIIM.sell_order_no
LEFT OUTER JOIN 
   RS_Sell_Order_Mapping AS SOM ON SOM.sell_order_no = SIIM.sell_order_no AND SIIM.product_id = SOM.product_id
LEFT OUTER JOIN 
   RS_Inventory_Master AS IM ON IM.product_id = SIIM.product_id
where 
   so_date between CAST(DATEADD(month, DATEDIFF(month, 0, so_date)-5, 0)AS DATE) and CAST(DATEADD(month, DATEDIFF(month, 0, so_date)-4, 0)AS DATE)

"The previous six months" is not the same as "only want January's data". Which do you want - all data since January, all data **from** January (and January only), or the "previous six months" which may have multiple interpretations (the current calendar month and 5 prior, the 6 calendar months prior to the current one, today's date minus 6 months to the day, other)? — alroc, Jun 10 '14 at 11:01
Duplicate, of at least [this question](http://stackoverflow.com/questions/1424999/get-the-records-of-last-month-in-sql-server), although none of the existing answers are ideal (now adding better answer). — Clockwork-Muse, Jun 10 '14 at 11:53

Tomalak · Answer 1 · 2014-06-10T12:04:39.930

1

Suppose current month is June then I only want January's data

This would work

WHERE 
  so_date >= DATEADD(mm, -6, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 8) + '01')
  AND
  so_date <  DATEADD(mm, -5, LEFT(CONVERT(VARCHAR, GETDATE(), 120), 8) + '01')

The LEFT(CONVERT(VARCHAR, GETDATE(), 120), 8) + '01' gives you the start of the current month in YYYY-MM-DD format. The rest is straight-forward.

edited Jun 10 '14 at 12:04

answered Jun 10 '14 at 11:03

Tomalak

332,285
67
532
628

First: I don't think we're close enough for you to call me "dude". Second: *"this is not working"* is anything but an error description. – Tomalak Jun 10 '14 at 11:10
Okay Sir thak you for your valuable time I got the solution – Hardik Parmar Jun 10 '14 at 11:24
I'd avoid [`BETWEEN` on SQL Server](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). Or when trying to query continuous-range types on **any** RDBMS, actually. – Clockwork-Muse Jun 10 '14 at 11:49
@Clockwork Hm, interesting read. I'm not sure if I follow the premise though. The author of the post does some stupid things and then complains that the results are unexpected. Like `WHERE CONVERT(SMALLDATETIME, OrderDate) BETWEEN '20110701' AND '20110731';` - I mean, WTF? Who in their right mind would do that? I do agree with the *"inclusive range"* argument and will change my query accordingly. – Tomalak Jun 10 '14 at 12:01
@Tomalak - In context, the author is demonstrating what happens if either the actual stored type changes, or if somebody is trying to make their current query "safe" via casting (never mind the fact that would probably cause indices to be ignored too). Personally I think `BETWEEN` is one of those "evil" things that should be removed from the standard (you want an inclusive range, you can explicitly ask for it, but it's only safe for integer types...). – Clockwork-Muse Jun 10 '14 at 12:13
@Clockwork-Muse I understand what the author is trying to say, but if you use your tools wrong, then the results won't be what you might expect. Using *that* as an argument against - in this case - `BETWEEN` is weak. But the blog post does have a point. *(As far as I am concerned, `BETWEEN` should represent the `[from_date,to_date[` range, that it represents `[from_date,to_date]` is unfortunate.)* – Tomalak Jun 10 '14 at 12:24
@Tomalak - But then people are going to be looking at their results funny(ier) if they're using it for negative numbers (the exclusive bound is the _lower_ one - `>` - in that case). – Clockwork-Muse Jun 10 '14 at 12:47
@Clockwork-Muse You're right. I think I'll be a lot more sensitive to `BETWEEN` issues from now on, thank you. :) – Tomalak Jun 10 '14 at 13:29
@Clockwork-Muse BTW your answer in the other thread shows a problem with SO in general: Late, good answers rarely receive the attention they deserve. I nudged the OP to mark your answer as accepted instead. – Tomalak Jun 10 '14 at 14:11

score 0 · Accepted Answer · 2014-06-10T11:26:45.650

0

To get all the data for a specific month (6 months ago) use the following where clause, You need to compare month and year, to ensure you get the correct month ie if the current month is May you want December from the previous year.

  where 
          datepart(Month, [so_date]) = datepart(Month, dateadd(month, -6,getdate())) 
       and
          datepart(Year, [so_date]) = datepart(year, dateadd(month, -6,getdate()))

edited Jun 10 '14 at 11:26

answered Jun 10 '14 at 11:08

1

This solution cannot use an index and will be unnecessarily slow on large tables. – Tomalak Jun 10 '14 at 11:33
Sorry,I did not got you – Hardik Parmar Jun 10 '14 at 12:08

score 0 · Answer 3 · answered Jun 10 '14 at 11:23

Please check the example, you use DateAdd function to minus the sixmonth and compare with Between functionality

Declare @t table (name varchar(50), createddate datetime)

Insert into @t values('ajay', GETDATE()-50 ),('ajay1', '2014-03-10' ),('ajay2', '2013-12-09' ),('ajay3', '2013-11-10' )
declare @currentdate datetime = getdate() , @sixmontholddate datetime = dateadd( MONTH, -6,getdate())

select @currentdate , @sixmontholddate

select  * from @t

select * from @t where createddate between dateadd( MONTH, -6,getdate()) and GETDATE()

This is [SQL Server](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common), let's not. Heck, you shouldn't be trying this with continuous-range types on any server. — Clockwork-Muse, Jun 10 '14 at 11:48

How to get only a particular month's data in SQL

3 Answers3