Passing a dd-MM-yyyy date as a parameter in a procedure

Hi!

When I execute this in SQL, it works fine:
call  EX.PROC_EXAMPLE ('09876','001234','18-04-2024',10000, 888);

Note 1: The third field ('18-04-2024) is a DATE in SQL.
Note 2: The third field has the format DD-MM-YYYY.

However, when I try to execute with a CallableStatement, it returns ORA-01843: not a valid month:
public void executeProcedureX(ParamsX params) throws Exception { CallableStatement callableStatement = null; Connection connection = null; try {                       // params.getDate() = "20240418"  - the yyyyMMdd format that I need to change SimpleDateFormat inputFormat = new SimpleDateFormat("yyyyMMdd"); SimpleDateFormat outputFormat = new SimpleDateFormat("dd-MM-yyyy"); Date utilDate = inputFormat.parse(params.getDate()); String formatDate = outputFormat.format(utilDate);                                               //formatDate =18-04-2024  - the right format dd-MM-yyyy that I need is ok and I need to store. connection = getConnection(); StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder.append("call EX.PROC_EXAMPLE(?,?,?,?,?)"); callableStatement = connection.prepareCall(sqlBuilder.toString()); callableStatement.setString(1, params.getContract());  //params.getContract() is a String callableStatement.setString(2, String.valueOf(params.getCode()));  // params.getCode() is an Integer callableStatement.setString(3, formatDate); // formatDate is an String callableStatement.setBigDecimal(4, params.getValue()); // params.getValue() is a BigDecimal callableStatement.setBigDecimal(5, params.getValueAnt()); // params.getValueAnt() is a BigDecimal logger.info("sqlBuilder:" +sqlBuilder); callableStatement.execute(); }catch (Exception e) { logger.error(e.getMessage(),e); throw new Exception(e); } finally { DatabaseConnectionManager.getInstance().closeResources(null, null, connection, callableStatement); } }

I don't understand why it returns  ORA-01843: not a valid month once the setString is being set with a String and the field is a DATE in SQL, I read it was supposed to work with the setString().

I tried also with setDate() passing a Date as params, but it didn't work also.


Could you help me?

luize casaretti wrote:I tried also with setDate() passing a Date as params, but it didn't work also.

That's the correct way to do it, passing in java.sql.Date.

How did you create the Date object used with setDate?  If you used the Date#valueOf method, the string would need to be formatted as yyyy-MM-dd.

For example:SimpleDateFormat inputFormat = new SimpleDateFormat("yyyyMMdd"); SimpleDateFormat sqlFormat = new SimpleDateFormat("yyyy-MM-dd");  // sql Date string value format Date utilDate = inputFormat.parse(params.getDate()); String sqlDateStr = sqlFormat.format(utilDate); java.sql.Date sqlDate = java.sql.Date.valueOf(sqlDateStr); ... callableStatement.setDate(3, sqlDate);

Edit: fixed typo

What number were you passing for month? Remember that 11 means December.

Campbell Ritchie wrote:What number were you passing for month? Remember that 11 means December.

Documentation for SimpleDateFormat:
MM    July ; Jul ; 07

But Date has 0‑based month numbers. Why are we stuck with java.sql.Date following that convention?

Campbell Ritchie wrote:But Date has 0‑based month numbers. Why are we stuck with java.sql.Date following that convention?

Because it inherits that method from java.util.Date. And, incidentally, it's a deprecated method.

More pædantically, getMonth()returns the month as an index, and Java indices are zero-based. If you want a type-safe month, you use the Calendar month.

Tim Holloway wrote:f you want a type-safe month, you use the Calendar month.

Are you referring to java.util.Calendar?  That doesn't seem very type-safe to me, so maybe you mean something else.  And calendar.get(Calendar.MONTH) still returns 0 for January.

I tried with:

callableStatement.setDate(3, new java.sql.Date(DateUtils.parseDate(formatDate, Constants.DATE_PATTERN_DDMMYYYY).getTime()));

but this new java.sql.Date(DateUtils.parseDate(formatDate, "ddMMyyyy").getTime() still returns yyyyMMdd

I just need to save the dd-MM-yyyy format

A java.sql.Date object doesn't have a format. But when you change it to a String then the changing process uses a default format, which naturally is the ISO standard format. If you're so unlucky as to have a different format requirement, then just provide a String which uses your desired format. Don't use a java.sql.Date object.

luize casaretti wrote:but this new java.sql.Date(DateUtils.parseDate(formatDate, "ddMMyyyy").getTime() still returns yyyyMMdd

No, it doesn't.  It returns a java.sql.Date object, which has a toString() method which is what you see if you try to print it out.  That toString() method is documented to always return a string in the format yyyy-mm-dd.  (For consistency with SimpleDateFormat it should say yyyy-MM-dd, but that's not what the API actually says; oh well.). Anyway, that object still represents the date you want.  It's just that when you look at it using toString(), it will print using the format yyyy-mm-dd.

Alternately, look at what gets stored in the database.  Is it the correct date, or not?  The way it gets represented in the DB will depend on your database.  If you don't like it there, you may have to store the field as a varchar in order to be able to specify the format you want.  But in a DB, it's usually best to store a date as a date, and stop worrying about the format.

Curses, foiled by Dr. C!

Note 1: The third field ('18-04-2024) is a DATE in SQL.

I see you have been receiving contradictory advice. The problem is this note, I think. In the version of SQL that I use (MySQL), that field isn't a DATE. I have to write "DATE(2024-18-04)" in order to get the date value which you use. If I write the string value '18-04-2024' then it's just a string value and it isn't interpreted as a date.

So maybe your stored procedure includes the processing which converts the string value to a date value?

Paul Clapham wrote:A java.sql.Date object doesn't have a format. ...

But the class does specify the format to use when creating an object based on a String representation: java.sql.Date#valueOf

The documentation wrote:public static Date valueOf(String s)
Converts a string in JDBC date escape format to a Date value.
Parameters:
s - a String object representing a date in in the format "yyyy-[m]m-[d]d". The leading zero for mm and dd may also be omitted.
Returns:
a java.sql.Date object representing the given date
Throws:
IllegalArgumentException - if the date given is not in the JDBC date escape format (yyyy-[m]m-[d]d)