sql to get next 3 months with given parameters

I have two values
Month = 01
Year = 2011

now I want to get next 2 months using the query
generally we know the next 2 months are 02/2011, 03/2011

similarly if user enters 11/2011 then next 2 months will be 12/2011, 01/2012

so i wrote the below query but it works only for 11th and 12th month but not for other months so can any one correct me...

select * from tbl
Where  tbl.calendaryr * 100 + tbl.monthval
      BETWEEN (year* 100 + month)
      AND ((year* 100 + month) + 90)

if year = 2011 and month = 01 then

select * from tbl
Where  tbl.calendaryr * 100 + tbl.monthval
      BETWEEN (2011 * 100 + 01)
      AND ((2011 * 100 + 01) + 90)

even the dateadd function requires date
DATEADD ( datepart , number, date )


if i had date parameter then i will do like below:

SELECT * FROM TABLE
WHERE myDATEFIELD BETWEEN @DateParameter and dateadd(month,3,@DateParameter )


I am out of all options...so may i know how to fix my query or is there  any other alternate solutions...
shragiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
From the userinput, simply prefix 01/  and you apply this logic
Where mydatefiel >=Convert(date,  '01/.' + @dateparameter ,103)
And mydatefield < dated(month,4, convert( daytime, '01/' +@dateparameter, 103))

That should do it.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I'm not a SQL Server person but can you not CONVERT the string to a date then do the date math with dateadd?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sorry I have a .  too much

The 4  should be a 3 ...
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
shragiAuthor Commented:
Actually I think I got it thanks for convert clue...

I can get the end month and end year as below and then i think its simple from there on...

YEAR(DATEADD(MONTH, 3, '2011'+'-'+'11'+'-'+'01'))
MONTH(DATEADD(MONTH, 3, '2011'+'-'+'11'+'-'+'01'))
0
 
LaccaCommented:
Hi.
Try this:

select ADD_MONTHS(to_date(Year ||Month ||'01','YYYYMMDD'),1)
     , ADD_MONTHS(to_date(Year ||Month ||'01','YYYYMMDD'),2)
from dual


or this:

select to_char(ADD_MONTHS(to_date('2011' ||'01' ||'01','YYYYMMDD'),1),'MM/YYYY')
     , to_char(ADD_MONTHS(to_date('2011' ||'01' ||'01','YYYYMMDD'),2),'MM/YYYY')
from dual


Regards, Lacca
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I also don't understand why you have put Lacca into accepted, as that code was for Oracle, and you are looking for SQL Server, so the syntax is different....
0
All Courses

From novice to tech pro — start learning today.