Solved

sql to get next 3 months with given parameters

Posted on 2014-02-12
10
555 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 77

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 41
semaphore timeout period has expired 1 32
T-SQL: Wrong Result 7 39
Group ordersum by orderdate 3 20
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
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.

751 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