Convert Mon yyyy to yyyy-mm-dd format in oracle

Mohsina 0
Viewed 36

I am sending date (August 2022) from java as a string to oracle plsql and i want to change the format from August 2022 to 01-08-22 in plsql. how can i do it ?

below is a part of code:

 PROCEDURE CHECK_DUMMY
(
        IN_SL_NO                         IN      SIGN.SL_NO%TYPE,
        IN_MONTH                    IN      SIGN.MONTH%TYPE
)
AS
    
        V_MON              DATE := TO_DATE(IN_MONTH , 'DD-MM-YYYY');
date plsql oracle
1 Answers

You want to use the format model Month YYYY and specify the language you are using:

CREATE PROCEDURE CHECK_DUMMY
(
  IN_SL_NO IN SIGN.SL_NO%TYPE,
  IN_MONTH IN SIGN.MONTH%TYPE
)
AS
  V_MON DATE := TO_DATE(IN_MONTH , 'Month YYYY', 'NLS_DATE_LANGUAGE=English');
BEGIN
  -- Do Something
  DBMS_OUTPUT.PUT_LINE( v_mon );
END;
/

Then:

BEGIN
  CHECK_DUMMY(1, 'August 2022');
END;
/

Outputs:

2022-08-01 00:00:00

Note: In Oracle, a DATE is a binary data type comprising of 7 bytes that represent century, year-of-century, month, day, hours, minutes and seconds and always has those 7 components and is never stored with any particular format. If you want to output 01-08-22 for display purposes then use TO_CHAR(v_mon, 'DD-MM-YY') to convert the date to a formatted string.

fiddle

MT00
Related