Solved

sql to get next 3 months with given parameters

Posted on 2014-02-12
10
526 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 142

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 142

Expert Comment

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

The 4  should be a 3 ...
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 142

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

832 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