Oracle/SQL LAST_DAY Function

Oracle LAST_DAY Function is used to return the last day of the month of the date passed.SQL LAST_DAY Function can be used with SQL SYSDATE. Also, Oracle LAST_DAY Function can be used with the SQL TRUNC Function to view the date component only if we have used SYSDATE as parameter.

Oracle LAST_DAY Function Syntax

SELECT LAST_DAY(date)
FROM table_name;

Oracle LAST_DAY – Using SQL SYSDATE Example

Oracle LAST_DAY can be used with the SQL SYSDATE pseudo column.

For example, the SQL query below returns the last day of of the month as per SYSDATE.

SELECT LAST_DAY(SYSDATE)
FROM DUAL;

May return something like “11/30/2012 2:31:34 PM” depending upon the system date.

Oracle LAST_DAY- Using String Example

SQL LAST_DAY can be used with data types other than Date.

For example, the below SQL query below returns the last day of the month passed as String.

SELECT LAST_DAY('01-Jan-2012') 
FROM DUAL;

Will return “1/31/2012”.

Oracle LAST_DAY – Using SQL TO_CHAR Function Example

SQL LAST_DAY can be used with the SQL TO_CHAR Function to format the output.

For example, the SQL query below returns the last day of the month with the month spelled completely.

SELECT TO_CHAR(LAST_DAY('01-Jan-2012'),'DD-MONTH-YYYY') 
FROM DUAL;

Will return “31-JANUARY-2012”

Oracle LAST_DAY – Using SQL SYSDATE and TRUNC Function Example

SQL LAST_DAY Function can be used with the SQL SYSDATE to return the Date and time components.

If we wish to view only Date component of last day of the month then we can use SQL TRUNC Function with SQL LAST_DAY.

For example, the SQL LAST_DAY query below returns the last day of the month without showing the time.

SELECT TRUNC(LAST_DAY(SYSDATE)) 
FROM DUAL;

May return “1/31/2012” depending upon the SYSDATE.