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

Question

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

Don’t send a string from Java. Send a `LocalDate` object. See [Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2](https://stackoverflow.com/questions/43039614/insert-fetch-java-time-localdate-objects-to-from-an-sql-database-such-as-h2). — Ole V.V., Sep 21 '22 at 05:38
yes i did that and that worked for me. but this is the requirement how can i achieve the solution in this way — Mohsina, Sep 21 '22 at 05:39
If you're passing a string like 'August 2022' you need to use this format 'Month yyyy'. It supposes that the columns MONTH in table SIGN is of type VARCHAR2. — gsalem, Sep 21 '22 at 06:48

MT0 · Answer 1 · 2022-09-21T07:50:38.397

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

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

1 Answers1