java.sql.SQLException: ORA-01843: not a valid month

Question

I am getting the following error when inserting data into my oracle database.

java.sql.SQLException: ORA-01843: not a valid month

In database date is as: dd-MMM-yy (06-MAR-12)
I am converting 06-03-2012 to dd-MMM-yy by the following method:

String s="06-03-2012";

String finalexampledt = new SimpleDateFormat("dd-MMM-yy").format(new SimpleDateFormat("dd-MM-yyyy").parse(s));

So i got 06-Mar-12 which is same as the above database date format still i am getting the error. I am inserting as:

in index.jsp

 String todaydate="";

Calendar calendar1 = Calendar.getInstance();
SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
todaydate = dateFormat.format(calendar1.getTime());

<input type="text" name="datename" value="<%=todaydate%>"/>

in servlet(doPost)

String s=request.getParameter("datename");

PreparedStatement ps=con.prepareStatement("insert into tablename(rest_dt, othercolname) values (to_date(?, 'dd-mm-yyyy'), ?)");

ps.setString(1, s);
ps.setString(2, otherstringdata);

int  rs=ps.executeUpdate();

Any idea please

@student: Are you sure that your input dates will always be like dd-MM-yyyy (British/French format), never MM-dd-yyyy (American format)? Can there ever be something in that input that isn't a date? — , Mar 06 '12 at 11:06
@user1143825 `("insert into mytablename (rest_dt) values (?)");` rest_dt is columan name which is in date type in database — saroj, Mar 06 '12 at 11:11
@MarkBannister ya input date type is dd-MM-yyyy, never MM-dd-yyyy — saroj, Mar 06 '12 at 11:12
@A.B.Cade same java code for insertion, i have mentioned just below your comment — saroj, Mar 06 '12 at 11:22
if the DB type is date why do you use `setString` and not `setTimestamp` with your date unconverted to a string ? — A.B.Cade, Mar 06 '12 at 11:40
no, finalexampledt is a String, you need a Date (if you use setDate) or a Timestamp (if you use a setTimestamp) — A.B.Cade, Mar 06 '12 at 12:14
@A.B.Cade i have updated my question , i have given in detail please see — saroj, Mar 06 '12 at 12:16
@MarkBannister no same error is coming, i have updated my question please see — saroj, Mar 06 '12 at 12:20
@student: try changing the format string in the servlet from `'dd-mm-yyyy'` to `'dd-MM-yyyy'`. — , Mar 06 '12 at 12:27
@MarkBannister ya i changed but same error is coming again, where i m wrong — saroj, Mar 06 '12 at 12:29
06-03-2012 is coming in servlet so i added `s` in set string but no result same error triggers again — saroj, Mar 06 '12 at 12:41
just out of curiosity: does `insert into tablename(rest_dt, othercolname) values (sysdate, ?)` works ? (of course with only `ps.setString(1, otherstringdata);`) — A.B.Cade, Mar 06 '12 at 13:11
I recommend you don’t use `SimpleDateFormat` and `Calendar`. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use `LocalDate` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Aug 08 '21 at 17:48

Ilya · Accepted Answer · 2012-03-06T11:51:51.583

6

so make

("insert into mytablename (rest_dt) values to_date(?, 'DD-MM-YYYY')");

Try this

TO_DATE(?, 'DD-MM-YYYY','NLS_DATE_LANGUAGE = American')

// gets from Oracle docs

edited Mar 06 '12 at 11:51

answered Mar 06 '12 at 11:16

Ilya

29,135
19
110
158

ya but i have more fields including date – saroj Mar 06 '12 at 11:20
did you use @user1143825 idea with- `setString(1, "06-03-2012")` ? – A.B.Cade Mar 06 '12 at 11:44
ya i tried but same exception is coming, m i doing any wrong in my insertion code? i have posted in my question – saroj Mar 06 '12 at 12:04
@user1143825 ya same error is coming , i have updated my question and have given in detail, please see – saroj Mar 06 '12 at 12:17

Eggi · Answer 2 · 2012-03-06T12:22:03.593

1

Problem is that oracle uses NLS_DATE_LANGUAGE to get the current name of the month. So you should do

select * from nls_session_parameters

and check if you have the correct values. You can also check with the following select which name you get for the month

select  TO_CHAR(TO_DATE('01-03-01', 'DD-MM-YY'), 'MON') from dual

I really don't understand why you insert the variable as a string value. Just use a date type (do the conversion on the client) in java and insert it without converting. If you really want to insert it as a string I would use a conversion to something like dd-MM-yyyy and insert it with TO_DATE(, 'DD-MM-YYYY').

Edit:

Do the conversion of the date on the client and use

ps.setDate(2, <yourDate>);

edited Mar 06 '12 at 12:22

answered Mar 06 '12 at 11:10

Eggi

1,684
4
20
31

So what is your output for the queries i posted? – Eggi Mar 06 '12 at 12:12
no same error is coming , i have updated my question and have given in detail, please see – saroj Mar 06 '12 at 12:18
i am using `to_date()` in `ps`, do i need to convert `s` to date type again? – saroj Mar 06 '12 at 12:27
I would do the conversion on the client because you save a roundtrip to the database. When your users input a incorrect date format the database is called and throws an exception. After that your show the error to your user and let him correct his input and you send the information again to the database. If you do the conversion directly on the client you can react faster. But if you want to do it that way: Have you looked into the values of your variables that you set your query to? – Eggi Mar 06 '12 at 13:03

score 1 · Answer 3 · answered Mar 06 '12 at 12:05

1

The datatype of your rest_dt columns is a DATE, so you need to supply one. You can use the TO_DATE function to convert a string to an Oracle DATE, so your insert statement

insert into tablename(rest_dt, othercolname) values (to_date(?, 'dd-mm-yyyy'), ?)

is fine.

Just make sure the string value you bind to your first ?-variable is in the format dd-mm-yyyy. And don't convert or format that value yourself: the TO_DATE function does that part.

There is no need to anything about session settings like nls_date_language here, since you have wisely chosen to use a language agnostic setting for the month with your MM mask (instead of MON).

Regards,
Rob.

answered Mar 06 '12 at 12:05

Rob van Wijk

17,555
5
39
55

ya same error is coming , i have updated my question and have given in detail, please see – saroj Mar 06 '12 at 12:17
Please remove all code that is converting your date. You write: "I am converting 06-03-2012 to ...". Just don't do it and supply that 06-03-2012 to your insert statement. – Rob van Wijk Mar 06 '12 at 12:53

Bharathiraja · Answer 4 · 2021-08-07T04:02:29.623

The same issue faced while running big query (multiple union) in Java and issue not with actual input since I have properly converted the with to_date('30-06-2021', 'dd-MM-yyyy') and found issue is with the date1 in query.

e.g.

select a,b,c from table1 where date1='31/12/2015'and date2=<actual input>
union
select a,b,c from table2 where date1='31/12/2015'and date2=<actual input>
union
select a,b,c from table3 where date1='31/12/2015'and date2=<actual input>
.
.

date1 also should be convert to to_date like below

e.g.

select a,b,c from table1 where date1=to_date('31/12/2015', 'dd-MM-yyyy') and date2=<actual input>

Hence issue resolved. My suggestions is, if you are getting such issues check the date part in the query and mention with to_date.

Java code:

@Autowired
private NamedParameterJdbcTemplate namedJdbcTemplate;

List<ResponseDTO> list = new ArrayList<>();
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("value1", dto.getValue1());
params.addValue("value2", dto.getValue2());

list = namedJdbcTemplate.query(SQL_QUERY, params, new CustomValueMapper());

Its purely only my own experience. Click up vote if it helps.

score 0 · Answer 5 · answered Aug 08 '21 at 18:02

java.time and JDBC 4.2

Don’t transfer a date as a string to or from your database. Transfer a proper date object. I am assuming that your JDBC driver is at least JDBC 4.2 compliant. About all drivers are these days. In this case LocalDate is the type to use for dates, both in your Java program and in the transfer to the database.

So what you basically need is this:

    LocalDate date = LocalDate.of(2012, Month.MARCH, 6);

    PreparedStatement ps = con.prepareStatement(
            "insert into tablename(rest_dt, othercolname) values (?, ?)");

    ps.setObject(1, date);
    ps.setString(2, otherstringdata);

    int rs = ps.executeUpdate();

If you are receiving your date as string input from JSP, immediately parse it into a LocalDate object. There’s no need to wait until you need to put it into your database.

    String inputString = "06-03-2012"; // Meaning 6 March 2012
    LocalDate date = LocalDate.parse(inputString, DATE_PARSER);

I have been using this formatter:

private static final DateTimeFormatter DATE_PARSER
        = DateTimeFormatter.ofPattern("dd-MM-uuuu", Locale.ROOT);

Links

Oracle tutorial: Date Time explaining how to use java.time.
Related question: Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2

java.sql.SQLException: ORA-01843: not a valid month

5 Answers5

java.time and JDBC 4.2

Links

Linked