Search
 
SCRIPT & CODE EXAMPLE
 
CODE EXAMPLE FOR SQL

ORACLE RANGE DATE USING CONNECT BY PER WEEK,MONTHS,ETC

 SELECT DISTINCT  TO_CHAR(z.START_DATE,'MMRRRR') MM  
            ,TO_CHAR(z.START_DATE,'DD-MON-RRRR') START_DATE 
            ,TO_CHAR(z.END_DATE,'DD-MON-RRRR') END_DATE
    FROM  (WITH C_RANGE AS   
    (SELECT TRUNC(:STARTDATE) + LEVEL - 1 FECHA_INI
                ,ADD_MONTHS(TRUNC(:STARTDATE),LEVEL) - 1 FECHA_FIN
        FROM DUAL 
    CONNECT BY LEVEL <= (TRUNC(:ENDDATE) - TRUNC(:STARTDATE)))
    SELECT DISTINCT
               (CASE WHEN L.FECHA_INI = TRUNC(:STARTDATE) THEN TRUNC(:STARTDATE) ELSE ADD_MONTHS(L.FECHA_FIN,-1) +1 END) START_DATE
              ,(CASE WHEN L.FECHA_FIN +1 >= TRUNC(:ENDDATE)  THEN  TRUNC(:ENDDATE) ELSE L.FECHA_FIN  END)  END_DATE  
      FROM  C_RANGE L
     WHERE ADD_MONTHS(L.FECHA_FIN,-1) <= TRUNC(:ENDDATE)
         AND  TRUNC(:ENDDATE) >= TRUNC(:STARTDATE)) z
   ORDER BY MM ASC ;
       
Source by stackoverflow.com #
 
PREVIOUS NEXT
Tagged: #ORACLE #RANGE #DATE #USING #CONNECT #BY #PER
ADD COMMENT
Topic
Name
4+1 =