Solved

sql to get next 3 months with given parameters

Posted on 2014-02-12
10
534 Views
Last Modified: 2014-03-03
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...
0
Comment
Question by:shragi
10 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39853757
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 39853763
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39853769
Sorry I have a .  too much

The 4  should be a 3 ...
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:shragi
ID: 39853796
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
 
LVL 1

Expert Comment

by:Lacca
ID: 39853904
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39863924
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
New and Previous Values in a Query 7 30
MS SQL Update query with connected table data 3 30
Run Stored Procedure uisng ADO 5 18
SQL View / Qtry 3 8
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question