Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Monday, April 8, 2013

SQL Fun : Connect By Level


My friend asked me to write a single SQL query which will return first day and last day of the month starting from today’s date till next 100 months.

Requirement: 

1-Apr-2013 30-Apr-2013

1-May-2013 31-May-2013

1-Jun-2013 30-Jun-2013

1-Jul-2013 31-Jul-2013

-------- ---------

-------- ---------


100th Record 100th Record

    Solution: 



SELECT LEVEL,
TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, LEVEL - 1), 'MM'),
'DD-MON-RRRR'
),
TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, LEVEL - 1)), 'DD-MON-RRRR')
FROM DUAL
CONNECT BY LEVEL <= 100;



We can get Last Day of month using LAST_DAY (SYSDATE) and First Day using TRUNC (SYSDATE, 'MM'). To repeat 100 times we can use CONNECT BY LEVEL





Result:



1 comment:


  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete