Show the First Day of the Month

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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

/

Show the Last Day of the Month

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

SELECT TRUNC (LAST_DAY (SYSDATE)) FROM DUAL;

/

Show the Number of Days in this Month

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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

/

Show the Remaining Days in the Month

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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

/

Show the FIrst Day of the Year

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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

/

Show the Number of Seconds So Far Today

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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

/

Show the Number of Seconds Left Today

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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

/

Show the Start Date and End Date of the Month

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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 '.')

/

Show the Number of Days Between Two Dates

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

SELECT ROUND ( (MONTHS_BETWEEN ('01-Jan-2000', '01-Jan-2010'), 0) FROM DUAL

/

Show the Last Day of the Year

SET PAUSE ON

SET PAUSE 'Press return to Continue'

SET LINESIZE 300

SET PAGESIZE 60

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

/