Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

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

Posted by Mir Sayeed Hassan on January 29th, 2023

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 =====