ORACLE SQL DATE FORMAT - Online Tech Support

This tutorial page is based on examples to be easier to follow. You can find Oracle Date Format types on this link. The TO_DATE syntax is following:

TO_DATE('date_value',['date_mask'],['NLS_DATE_FORMAT']);

The first example to use date format in Oracle would be as the following example:

SELECT TO_DATE('20120921','YYYYMMDD')
  FROM DUAL;

On the example above the Oracle date format has set a date mask after string ‘20120921’. The date mask is a text that is written or formatted in the same order as is the date value. For example the example has year as “YYYY” + month as “MM” + day number as “DD“. And the Oracle date value is September 21st 2012.

To find out the last date of some year you could do it as the following example. In that example we are looking for the last day number for year “2000“.

SELECT TO_DATE('20010101','YYYYMMDD') -1
  FROM DUAL;

The Oracle SQL has date the January 1st 2001 and we are subtracting one day off from it. In addition, you can use the same way to find out the last day of the February month. The reason to do it is because the February month has a different amount of days in every forth year. The example below with Oracle date format will return the last day of February 1998 and it is “28-FEB-98“.

SELECT TO_DATE('19980301','YYYYMMDD') -1
  FROM DUAL;

To achieve the last day of February 1998 we took the next month’s first day (March 1st 1998) and subtracted 1 day.

For additional oracle date format types please see the first link above and find out more date format oracle types.