First Day of the YEAR : ROUND (DATE)

First Day of the YEAR by ROUND function

This function works similar to ROUND of numbers, if the month is geater than six (6) then return the day of next year else it returns day of current year.

SELECT ROUND (SYSDATE, 'YEAR') "As of day" FROM DUAL;
/
AS OF DAY
1/1/2021
SELECT ROUND (TO_DATE ('12-mar-2021'), 'YEAR') "Current Year" FROM DUAL;
/
CURRENT YEAR
1/1/2021
SELECT ROUND (TO_DATE ('12-aug-2021'), 'YEAR') "New Year" FROM DUAL;
/
NEW YEAR
1/1/2022

First and Last Day of the year

SELECT TRUNC (SYSDATE, 'YEAR') "First Day" FROM DUAL;
FIRST DAY
1/1/2021
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Last Day" FROM DUAL;
LAST DAY
12/31/2021

First and last Day of Month


SELECT TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1) FIRST_DAY FROM DUAL;
FIRST_DAY
9/1/2021
SELECT   ADD_MONTHS (
            TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 1),
            1)
       - 1 LAST_DAY
  FROM DUAL;
LAST_DAY
9/30/2021

OR

 SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1,
      TRUNC(LAST_DAY(SYSDATE)) 
 FROM DUAL;

OR

 SELECT TRUNC(LAST_DAY(SYSDATE)) LAST_D FROM DUAL;

comments powered by Disqus