Fisrt day of month in ORACLE

The Function below retrieves the first day of month with SYSDATE as input parameter in oracle. 

This also shows the implementation of FUNCTION in ORACLE

Function: 

CREATE OR REPLACE FUNCTION FirstDAY_of_Month(value_in DATE)

RETURN DATE IS

 VarMonth VARCHAR2(2);
 VarYear VARCHAR2(4);

BEGIN

  VarMonth := TO_CHAR(value_in, 'MM');

  VarYear  := TO_CHAR(value_in, 'YYYY');

  RETURN TO_DATE(VarMonth || '-01-' || VarYear, 'MM-DD-YYYY');

EXCEPTION

  WHEN OTHERS THEN

    RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');

END FirstDAY_of_Month;


Function calling example:

(
FirstDAY_of_Month(sysdate),'DD-Mon-YYYY')
 

No comments:

Post a Comment