CODEWITHSUNDEEP
sql-server-2008t-sql
Niladri Biswas
Niladri Biswas

Reputation: 4171

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

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

Upvotes: 2

Views: 3682

Answers (4)

Daniel
Daniel

Reputation: 632

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()

Upvotes: 0

Tirumudi
Tirumudi

Reputation: 443

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

Upvotes: 0

dani herrera
dani herrera

Reputation: 51655

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

Upvotes: 0

David Hedlund
David Hedlund

Reputation: 129792

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())

Upvotes: 4

Related Questions