Improve company productivity with a Business Account.Sign Up

x
?
Solved

sql to get next 3 months with given parameters

Posted on 2014-02-12
10
Medium Priority
?
649 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
6 Comments
 
LVL 79

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 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 1200 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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
Viewers will learn how the fundamental information of how to create a table.

584 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