How to get 6 months old data given only month and year from current date?

Question

I have a table that contains only Month and Year field both as int. Given an input say month, 6 months. I need to project the data that are 6 months old.

for example I have data from 2001 , 1st Janaury till date. Given input as 6 months , so I have to go back 6 months from cuurent date to get the result .

Output will be Data From Jan 2001 to Jan 2012.

I have done the program using the right,substring, string comparison.(a nasty hack)

Is there any prominent way of doing so?

DDL

Declare @t table (Mnth int, Yr int)
Insert Into @t 

-- 2011
Select 1,2011 Union All select 2,2011 Union All Select 3, 2011 Union ALL Select 4,2011 Union ALL
Select 5,2011 Union All select 6,2011 Union All Select 7, 2011 Union ALL Select 8,2011 Union ALL
Select 9,2011 Union All select 10,2011 Union All Select 11, 2011 Union ALL Select 12,2011 Union ALL

--2012
Select 1,2012 Union All select 2,2012 Union All Select 3, 2012 Union ALL Select 4,2012 Union ALL
Select 5,2012 Union All select 6,2012 Union All Select 7, 2012 Union ALL Select 8,2012 Union ALL
Select 9,2012 Union All select 10,2012 Union All Select 11, 2012 Union ALL Select 12,2012

Declare @inputMonth int = 6

I am trying without string conversion

Select Mnth,Yr,YEAR(DATEADD(mm,-@inputMonth,getdate())),MONTH(DATEADD(mm,-@inputMonth,getdate()))
From @t
WHERE   YEAR(DATEADD(mm,-@inputMonth,getdate())) < Yr
AND MONTH(DATEADD(mm,-@inputMonth,getdate())) < Mnth

But it is not working

Thanks

score 4 · Accepted Answer · edited May 23 '17 at 10:34

You could always convert to datetime and compare that.

Something like:

WHERE
    CONVERT(Datetime, 
        CONVERT(nvarchar(4), YearPart) + '-' + CONVERT(nvarchar(2), MonthPart) + '-01')
    ) > DATEADD(month, -6, GETUTCDATE())

i.e. concatenate your two columns to yield CONVERT(Datetime, "2011-1-01"), for instance.

As this answer to another question points out, ify ou don't want to do string conversions, you would have to add to do a series of DATEADD to 0, which is 1900-01-01.

WHERE
    DATEADD(month, MonthPart-1, DATEADD(year, YearPart-1900, 0))
    > DATEADD(month, -6, GETUTCDATE())

Is it possible to do that without a string conversion? – Niladri Biswas Jul 30 '12 at 12:21 — Niladri Biswas, Jul 30 '12 at 12:21

score 0 · Answer 2 · answered Jul 30 '12 at 12:27

0

Without casting.

Select *
From your table
Where  (yr= @someYear and mt between @someMonth-6 and @someMonth) or
       (@someMonth <6 and yr =@someYear - 1 and mt >= 12-(6-@someMonth) )

answered Jul 30 '12 at 12:27

dani herrera

48,760
8
117
177

Daniel · Answer 3 · 2012-07-30T13:46:46.093

0

Realized I hadn't thought it through, new solution:

declare @m int 
set @m = 11

declare @startDate datetime
set @startDate =  dateadd(mm,-@m,getdate())
select yr,mnth
from t
where
DateAdd(day, 1, 
      DateAdd(month, mnth - 1, 
          DateAdd(Year,yr-1900, 0))) >@startDate 
and
DateAdd(day, 1, 
      DateAdd(month, mnth - 1, 
          DateAdd(Year,yr-1900, 0)))  < getDate()

edited Jul 30 '12 at 13:46

answered Jul 30 '12 at 12:28

Daniel

622
4
6

score 0 · Answer 4 · answered Jul 30 '12 at 13:01

try this..

DECLARE @CurrentMonth tinyint,@CurrentYear tinyint
SELECT @currentMonth=Datepart(mm,getdate())
SELECT @CurrentYear=Datepart(yy,getdate())

IF((@currentmonth-6)<0)
 begin
  SELECT month,year FROM @t WHERE year =(@CurrentYear-1) AND month >(@currentMonth+6) 
   UNION ALL
  SELECT month,year FROM @t WHERE year=@CurrentYear AND month <= @CurrentMonth 
 end
ELSE
 begin
  SELECT month,year from @t WHERE year=@CurrentYear AND month between @CurrentMonth-6
  AND @CurrentMonth+1
 end

How to get 6 months old data given only month and year from current date?

4 Answers4