Date conversion format Java to DB different format

Question

I'm making a code in java that executes an Oracle database procedure The format I have to put there in the procedure when I run through the database is dd/MM/yyyy. I have to send this date from my java code using a CallebleStatement setDate with the date yyyy-MM-dd (which is the Date format in java) When this CallebleStatement is executed, will it transform the date I'm sending to the database format, or will it write to the Java format?

EDIT: It is basically this:

if(types[1].compareTo("DATE") == 0) {//procedureValues[i] = yyyy-MM-dd HH:mm:ss
    cs.setDate(i+1, Date.valueOf(procedureValues[i].trim()));
}else {
    cs.setObject(i+1, procedureValues[i].trim(), SQLTypes.valueOf(types[1]).getTypes());    
}

And yes, the procedure expects the DATE format

Are you aware of SimpleDateFormat? If you provide a sample of your code, we might provide an answer — procra
– procra, Commented Feb 1, 2022 at 14:15
Is the parameter for your DB procedure defined as a date, or as a string? If it's a date (as it should be, and as your use of setDate implies) then you don't need to worry about 'format'. — Alex Poole
– Alex Poole, Commented Feb 1, 2022 at 14:36
@procra Please don’t suggest the long outdated and notoriously troublesome SimpleDateFormat class. We have so much better in java.time, the modern Java date and time API, and its DateTimeFormatter. — Anonymous
– Anonymous, Commented Feb 2, 2022 at 5:18
There are some misunderstandings in your question. You don’t have to pass the date in any particular format. And you don’t have to use setDate(), Since JDBC 4.2 the recommended way is to use setObject() and pass a LocalDate to it. And not worry about its format. — Anonymous
– Anonymous, Commented Feb 2, 2022 at 5:19

MT0 · Accepted Answer · 2022-02-01 14:41:29Z

2

In Oracle, a DATE is a binary data type composed of 7 bytes (century, year-of-century, month, day, hour, minute and second). It ALWAYS has those components and it NEVER stores a format.

When this CallebleStatement is executed, will it transform the date I'm sending to the database format, or will it write to the Java format?

If you are using CallableStatement.setDate(), it will transform it to the binary values required by the database. It will NOT store it in an (easily) human readable format.

If you want to see how the database stores the value then you can use:

SELECT DUMP(your_date_column) FROM your_table;

When you read the value from the database then the client application (in this case, Java via the database driver) will read the binary values from the database and will convert it to a representation suitable to the client application.

If you read it in a different client application then the binary values will be converted to the appropriate representation for that client application; so two different client applications (for example, Java and SQL/Plus) may display the same binary date value with different formats.

edited Feb 1, 2022 at 14:41

answered Feb 1, 2022 at 14:35

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Giovanna Cazelato Pires Over a year ago

This was exactly what I believed was happening. And in the end, the error was a business rule and not formatting the date.... Thank you for your patience and for the well-explained answer.

Collectives™ on Stack Overflow

Date conversion format Java to DB different format

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related