Aug 122014
 

It is often asked in interview to write a SQL query that displays months between two dates. Suppose we are given with start-date and end-date and to fetch all the months that exist between start-date and end-date need to be displayed.

(Suppose Start Date=’11-JAN-2013’ and End Date=10-JAN-2014’)

Query 1: To display date in format “MON-YY”

SELECT

TO_CHAR(ADD_MONTHS(TO_DATE(’11-JAN-2013′,’DD-MON-YYYY’),ROWNUM-1),’MON-YY’)”DATE”

FROM   DUAL

CONNECT BY

ADD_MONTHS(TO_DATE(’11-JAN-2013′,’DD-MON-YYYY’),ROWNUM-1)<=(TO_DATE(’10-JAN-2014′,’DD-MON-YYYY’));

Output:

DATE
13-Jan
13-Feb
13-Mar
13-Apr
13-May
13-Jun
13-Jul
13-Aug
13-Sep
13-Oct
13-Nov
13-Dec

 

The above query didn’t display you the correct result because “14-JAN” result is not included in the output. So if the day number in the end date is less the day number in the start date, the result provided by the query will always be wrong.

Please find below the correct query:

SELECT

TO_CHAR(ADD_MONTHS(TO_DATE(’11-JAN-2013′,’DD-MON-YYYY’),ROWNUM-1),’MON-YY’)”DATE”

FROM   DUAL

CONNECT BY

ADD_MONTHS(TO_DATE(’11-JAN-2013′,’DD-MON-YYYY’)-(EXTRACT(DAY FROM TO_DATE(’11-JAN-2013′,’DD-MON-YYYY’))-1),ROWNUM-1)<=(TO_DATE(’10-JAN-2014′,’DD-MON-YYYY’)) 

Output:

DATE
JAN-13
FEB-13
MAR-13
APR-13
MAY-13
JUN-13
JUL-13
AUG-13
SEP-13
OCT-13
NOV-13
DEC-13
JAN-14

 

Query 2: To display date in format “MON-YYYY”

SELECT

TO_CHAR(ADD_MONTHS(TO_DATE(’01-JAN-2013′,’DD-MON-YYYY’),ROWNUM-1),’MON-YYYY’)”DATE”

FROM   DUAL

CONNECT BY

ADD_MONTHS(TO_DATE(’01-JAN-2013′,’DD-MON-YYYY’)-(EXTRACT(DAY FROM TO_DATE(’01-JAN-2013′,’DD-MON-YYYY’))-1),ROWNUM-1)

<=(TO_DATE(’31-JAN-2014′,’DD-MON-YYYY’))

Output:

DATE
JAN-2013
FEB-2013
MAR-2013
APR-2013
MAY-2013
JUN-2013
JUL-2013
AUG-2013
SEP-2013
OCT-2013
NOV-2013
DEC-2013
JAN-2014

 

Query 3: To display first day of every month in format “DD-MON-YYYY”.

SELECT

TO_CHAR(ADD_MONTHS(TO_DATE(’01-JAN-2013′,’DD-MON-YYYY’),ROWNUM-1),’DD-MON-YYYY’)”DATE”

FROM   DUAL

CONNECT BY

ADD_MONTHS(TO_DATE(’01-JAN-2013′,’DD-MON-YYYY’)-(EXTRACT(DAY FROM TO_DATE(’01-JAN-2013′,’DD-MON-YYYY’))-1),ROWNUM-1)

<=(TO_DATE(’31-JAN-2014′,’DD-MON-YYYY’))

Output:

DATE
01-JAN-2013
01-FEB-2013
01-MAR-2013
01-APR-2013
01-MAY-2013
01-JUN-2013
01-JUL-2013
01-AUG-2013
01-SEP-2013
01-OCT-2013
01-NOV-2013
01-DEC-2013
01-JAN-2014

Where,

CONNECT BY clause specifies the relationship between parent rows and child rows of the hierarchy.

ROWNUM: is a pseudocolumn to represent ordering of a row selected from a query.

EXTRACT: is a function to get day, month, year, hour, minute or second from a date value.

ADD_MONTHS: is a function that returns a date plus n months.

The syntax for the Oracle/PLSQL ADD_MONTHS function is:

ADD_MONTHS(date1,n) where

Parameters or Arguments

date1 is the starting date (before the n months have been added).

n is the number of months to add to date1.

So, multiple scenarios can be generated by just changing the date format.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)