Mir Sayeed Hassan – Oracle Blog

Useful tips to find the Sysdate Commands from Oracle Database by issuing SQL Queries

Find the First Day of the Month

SQL> SELECT TRUNC (SYSDATE, 'MONTH') FROM DUAL;

TRUNC(SYS
---------
01-JAN-23

Find the Last Day of the Month

SQL> SELECT TRUNC (LAST_DAY (SYSDATE)) FROM DUAL;

TRUNC(LAS
---------
31-JAN-23

Find the Number of Days in this Month

SQL> SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) FROM DUAL;

CAST(TO_CHAR(LAST_DAY(SYSDATE),'DD')ASINT)
------------------------------------------
31

Find the Remaining Days this Month

SQL> SELECT SYSDATE,LAST_DAY (SYSDATE) "Last",LAST_DAY (SYSDATE) - SYSDATE FROM DUAL;

SYSDATE      Last LAST_DAY(SYSDATE)-SYSDATE
-------------------------------------------
22-JAN-23     31-JAN-23 9

Find the First Day of the Year

SQL> SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;

TRUNC(SYS
---------
01-JAN-23

Find the Number of Seconds So Far Today

SQL> SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 FROM DUAL;

(SYSDATE-TRUNC(SYSDATE))*24*60*60
---------------------------------
33464

Find the Number of Seconds Left Today

SQL> SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 FROM DUAL;

(TRUNC(SYSDATE+1)-SYSDATE)*24*60*60
-----------------------------------
52851

Find the Start Date and End Date of the Month

SQL> SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date, TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date FROM XMLTABLE ('for $i in 0 to xs:int(D) return $i'PASSING XMLELEMENT (d,FLOOR (MONTHS_BETWEEN (ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),SYSDATE)))COLUMNS i INTEGER PATH '.');

START_DAT END_DATE
-------------------
01-JAN-23 31-JAN-23
01-FEB-23 28-FEB-23
01-MAR-23 31-MAR-23
01-APR-23 30-APR-23
01-MAY-23 31-MAY-23
01-JUN-23 30-JUN-23
01-JUL-23 31-JUL-23
01-AUG-23 31-AUG-23
01-SEP-23 30-SEP-23
01-OCT-23 31-OCT-23
01-NOV-23 30-NOV-23
01-DEC-23 31-DEC-23

12 rows selected.

Find Number of Months Between Two Dates

SQL> SELECT ROUND ( (MONTHS_BETWEEN ('01-Jan-2023', '01-Feb-2023'))) from dual;

ROUND((MONTHS_BETWEEN('01-JAN-2023','01-FEB-2023')))
----------------------------------------------------
-1

Find the Last Day of the Year

SQL> SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL;

Year Last
---------
31-DEC-23

===== Hence tested & verified in your test env =====