why a valid date insertion throws java.sql.SQLDataException: ORA-01843: not a valid month oracle java

Question

i am trying to insert date value into the oracle database but its throw java.sql.SQLDataException: ORA-01843: not a valid month the weirdest thing that happened, when i open oracle dashboard and i execute the same query into sql commands its work perfectly but when try to execute the query from java it launch the exception

this is my code

try {
        
            Connection con=Connexion.getConnection();
            Statement stmt=con.createStatement();
            Format formatter = new SimpleDateFormat("MM/dd/YYYY");
            String ddebut=formatter.format(dateDebut);
            String dfin=formatter.format(dateFin);

            System.out.println(ddebut);
            System.out.println(dfin);
            String query ="INSERT INTO Intervention (titre,lieu,etat,description,datedebut,datefin,ide) VALUES ('"+titre+"','"+lieu+"','"+etat+"','"
                    +description+"','"+ddebut+"','"+dfin+"',"+cin+")";
            System.out.println(query);

            test=stmt.executeUpdate(query);
            System.out.println(formatter.format(dateDebut));
            ResultSet rs=stmt.executeQuery("select intervention_seq.currval from DUAL");
            rs.next();
            id=rs.getInt(1);
        
        
            con.close();  
        
    

        }catch(Exception e){ System.out.println(e);}
        return test==1;
    }

im sure that the problem come from the query and all values of attribute are correct

is there a way to solve this problem ?

Two recommendations: (1) Don’t use `SimpleDateFormat` and `Date`. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use `LocalDate` from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). (2) Don’t pass date as a string to Oracle. Pass a `LocalDate` and format won’t matter. 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., May 02 '21 at 09:00
Also concatenating values into your SQL statement makes it liable to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). Use `?` plaveholders and parameter values as explained in [the tutorial](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). — Ole V.V., May 02 '21 at 09:50

score 1 · Answer 1 · answered May 02 '21 at 01:21

1

It's almost surely an NLS issue. Try supplying your dates as '20/05/2021' and '19/05/2021'. Each client (for example, Oracle dashboard and your Java program are two different clients) can have different NLS settings. These govern, among other things, the default date and time formatting.

answered May 02 '21 at 01:21

Mark92630

55
5

first of all thank you for your reply , when i execute this query in oracle to get the format SELECT value FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT'; it display mm/dd/yyyy and i have tried many format but the problem still exist. – Ghassen Jebari May 02 '21 at 01:29

daoern · Accepted Answer · 2021-05-02T01:46:24.823

1

Oracle accepts date formatted in DD-MON-RR (sometimes DD-MON-YYYY) by default. However, you've specified MM/dd/YYYY.

Solution 1
You can simply fix this by changing the date format from this,
new SimpleDateFormat("MM/dd/YYYY")
to this,
new SimpleDateFormat("dd-MMM-yy") (If the default format is DD-MON-RR)
or this,
new SimpleDateFormat("dd-MMM-yyyy") (If the default format is DD-MON-YYYY)

Note: You can check the default format simply by just select sysdate from dual; and see what does it returns. If it shows something like 01-MAY-21, then the default date format for your Oracle should be DD-MON-RR.

Solution 2
Keep your SimpleDateFormat, but changes the date format at your SQL statement by using to_date('date here', "MM/DD/YYYY")

edited May 02 '21 at 01:46

answered May 02 '21 at 01:32

daoern

163
2
9

thank you for your answer it was very helpful , i tried the first solution and it solve my problem the SYSDATE in oracle was 05/02/2021 not the same format but it works – Ghassen Jebari May 02 '21 at 01:59
2

The default date format varies by region. I don't know Java but surely you don't need to pass all values as strings? – William Robertson May 02 '21 at 09:19
2

@WilliamRobertson You are correct, passing date values as strings is the discouraged way (since JDBC 4.2 you should prefer to pass `LocalDate` objects). – Ole V.V. May 02 '21 at 09:36
Thanks for pointing it out! I didn't know about this. Out of curiosity, may I ask actually why should we prefer `LocalDate` over date strings in SQL? Is it because of performance issue? – daoern May 02 '21 at 10:19
@daoern Partly for the same reasons why you use `int` rather than `String` for a whole number: It reduces the risk of errors, is makes the code more self explanatory and it offers a range of operations that you may not need at the moment but may need some other time. Also the use of `LocalDate` would completely have prevented the problem that the OP is asking about. These considerations are far more important than performance in 19 out of 20 cases. So while formatting the string and parsing it back behind the scenes in the database does cost, this is a minor point in the bigger picture. – Ole V.V. May 02 '21 at 18:54

why a valid date insertion throws java.sql.SQLDataException: ORA-01843: not a valid month oracle java

2 Answers2