SQL Query to Fetch Order-wise Month from Date Column in Oracle

Query to fetch order-wise “Month” from Date column in an Oracle Table:

Step 1: Create a Table “Test” as shown below:

CREATE TABLE TEST (A DATE);

Step 2: Insert few random Date values into “TEST” Table.

INSERT INTO TEST VALUES('01-JAN-12');

INSERT INTO TEST VALUES('11-FEB-12');

INSERT INTO TEST VALUES('21-MAR-12');

INSERT INTO TEST VALUES('30-APR-12');

INSERT INTO TEST VALUES('01-MAY-12');

INSERT INTO TEST VALUES('02-JUN-12');

INSERT INTO TEST VALUES('06-JUL-12');

INSERT INTO TEST VALUES('10-AUG-12');

INSERT INTO TEST VALUES('30-SEP-12');

INSERT INTO TEST VALUES('28-OCT-12');

INSERT INTO TEST VALUES('18-NOV-12');

INSERT INTO TEST VALUES('31-DEC-12');

Step 3: Select the “TEST” Table.

SELECT * FROM TEST;

A

30-Apr-12

10-Aug-12

31-Dec-12

11-Feb-12

1-Jan-12

6-Jul-12

2-Jun-12

21-Mar-12

1-May-12

18-Nov-12

28-Oct-12

30-Sep-12

Step 4: Run the below query to fetch “MONTH” from Date Column “Order-wise”.

SELECT TO_CHAR(A,'MON') FROM TEST ORDER BY TO_CHAR(A,'MM');

Output:

TO_CHAR(A,'MON')

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC