Click here to Skip to main content
14,268,557 members
Rate this:
Please Sign up or sign in to vote.
I want to make a select that gives me a range of months between a period of 3 months.
Like that:
MONTH | YEAR
-----------------------
08 | 2019
11 | 2019
02 | 2020
05 | 2020
08 | 2020


What I have tried:

i want result like this for quarterly report purpose
Posted
Updated 8-Aug-19 5:41am
Comments
CHill60 8-Aug-19 7:35am
   
The "What I have tried:" section is for you to put the code that you have tried. We don't write code to order.
OriginalGriff 8-Aug-19 8:19am
   
What have you tried?
Where are you stuck?
What help do you need?
Benktesh Sharma 8-Aug-19 8:25am
   
Are you looking at raw dates and get the range of months beginning from the first date? Or you just want to create a list starting with 08/2019 and values 3 months after each of those?

Also, are you looking in strictly SQL or C# or other language?
OriginalGriff 8-Aug-19 9:57am
   
Hint: That's why questions have tags on them ...
Benktesh Sharma 8-Aug-19 10:29am
   
:). The tags are Oracle, VB.NET, ASP.NET4 and that's why I asked a language-specific question.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Oracle, like most SQL databases; has a BETWEEN condition which would probably be a good place to start.
SELECT *
FROM order_details
WHERE order_date BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');
Reference: Oracle / PLSQL: BETWEEN Condition[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Perhaps a recursive CTE is what you're after, something like
WITH Months (ActualDate) AS (
   SELECT TO_DATE('01.08.2019', 'DD.MM.YYYY') AS ActualDate
   FROM Dual
   UNION ALL
   SELECT ADD_MONTHS(a.ActualDate, 3)    AS ActualDate
   FROM   Months a
   WHERE  a.ActualDate <= TO_DATE('01.08.2020', 'DD.MM.YYYY')
)
SELECT m.ActualDate,
       TO_CHAR(m.ActualDate, 'MM') AS Month,
       TO_CHAR(m.ActualDate, 'YYYY') AS Year
FROM   Months m;
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100