Date: Find First Day Of The Month

Description: Returns the first day of a given month.

CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
 
RETURN DATE IS
 vMo VARCHAR2(2);
 vYr VARCHAR2(4);
BEGIN
  vMo := TO_CHAR(value_in, 'MM');
  vYr := TO_CHAR(value_in, 'YYYY');
  RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
  WHEN OTHERS THEN
    RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/
 
 
/*
Stuart S. sent in this comment and code:
It seems to me that there is no need to create a
function for this and string manipulation is a
somewhat inefficient method of doing this. The exact
same result can be obtained by simply truncating the
date to the month, without requiring a function to be
created:
*/
 
TRUNC(<date>,'MM')
 
SELECT TRUNC(datecol1,'MM') FROM t;
 
SELECT TRUNC(SYSDATE,'MM') FROM dual;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.