Convertion to java.sql.Date for dd-MMM-yy format

Question

I am doing jdbc and calling a procedure with date parameter but my db tables date format is in dd-MMM-yy format hence i converted my string date into dd-MMM-yy format but I am unable to setDate(1,sdt) cuz sdt must be in java.sql.Date type and java.sql.Date format is yyyy-MM-dd hence I need help

My procedure is defined thus:

PROCEDURE pStoreData(d_sumDttm IN DATE, i_Retval out number);

Short Code:

    System.out.print("Enter report date:");
    String sdate = scanner.nextLine();
    final Date date = new Date();
    final SimpleDateFormat format = new SimpleDateFormat();
    format.applyPattern(dd-MMM-yy);
    final String sysdt = format.format(date);
    java.sql.Date sqldt = java.sql.Date.valueOf(sysdt);
    callablestate = connection.prepareCall("{call Report.pStoreDate(?,?)}");
    callablestate.setDate(1,sqldt);
    callablestate.registerOutParameter(2,Types.REF_CURSOR);
    callable.execute();

The database's date format is irrelevant if you insert using `java.sql.Date`, so forget all about date formats and insert the date value. The date format only matters if you insert the date value as a `String`, and I'd strongly discourage you from doing that. — Andreas, Aug 09 '20 at 19:11
I am taking a date from user thats why its in a string format. — Arhana, Aug 09 '20 at 19:27
Parse the date from the user into a `LocalDate` and after that don’t care about format any more. Neither a `LocalDate` nor a `date` in SQL has got any format (that you need to care about). — Ole V.V., Aug 09 '20 at 19:47
Possibly related: (1) [Best way to convert Java SQL Date from yyyy-MM-dd to dd MMMM yyyy format](https://stackoverflow.com/questions/3178690/best-way-to-convert-java-sql-date-from-yyyy-mm-dd-to-dd-mmmm-yyyy-format) (2) [How to convert Oracle Date format to Java? \[duplicate\]](https://stackoverflow.com/questions/61279889/how-to-convert-oracle-date-format-to-java) — Ole V.V., Aug 09 '20 at 19:50
@Ole I have parsed date and stored in a variable ld and when i am trying callablestatement.setDate(1,ld), i am having an error "setDate(String,Date) in the type CallableStatement is not applicable for the arguments (LocalDate) . — Arhana, Aug 09 '20 at 21:29
@Arhana Did you even look at the very nice answer that shows you need to call `setObject()` for a `LocalDate`, not `setDate()`? — Andreas, Aug 10 '20 at 00:35
@Andreas i have tried that which caused me "java.time.DateTimeParseException : Text '10-08-20' could not be parsed at index 3 — Arhana, Aug 10 '20 at 03:49
You tried what? You haven't put any code in the question to show what you've tried. But, let me guess, you call [`LocalDate.parse("10-08-20")`](https://docs.oracle.com/javase/8/docs/api/java/time/LocalDate.html#parse-java.lang.CharSequence-) even the the *documentation* of that method specifically says the format must be `"2020-10-08"`, so of course it fails. Why would you expect otherwise? — Andreas, Aug 10 '20 at 04:17
@Andres how are you expecting me to use setObject when i have already written in a question that i am calling a procedure with date type parameter. — Arhana, Aug 10 '20 at 04:40
You’re almost there. `callablestatement.setObject(1,ld)` should do it (unless there’s something relevant that you haven’t told us). — Ole V.V., Aug 10 '20 at 05:13
@Andreas - Thanks for your valuable inputs. I can see that the question has received one vote for deletion. I agree that OP has not shown any effort which makes it eligible to be voted for closure, but sometimes new contributors do not know this fact. Deleting this question will waste all the effort put by you, Ole V.V. and me. If you agree that it deserves another chance, please vote to open it. — Arvind Kumar Avinash, Aug 10 '20 at 20:06
@OleV.V. - Thanks for your valuable inputs. I can see that the question has received one vote for deletion. I agree that OP has not shown any effort which makes it eligible to be voted for closure, but sometimes new contributors do not know this fact. Deleting this question will waste all the effort put by you, Andreas and me. If you agree that it deserves another chance, please vote to open it. — Arvind Kumar Avinash, Aug 10 '20 at 20:07
@Arhana The JDBC driver will convert the value you give to the appropriate data type needed by the SQL statement in question. If the statement needs a date value, the driver will convert the value as needed, e.g. it will convert a `java.sql.Date` if you call `setDate()`, a `java.sql.Timestamp` if you call `setTimestamp()`, a `String` if you call `setString()` using date format of the server, and any type the JDBC driver recognizes if you call `setObject()`, such as values of type `Date`, `Timestamp`, `LocalDate`, `ZonedDateTime`, `Instant`, etc. Make sure you use the latest version of driver. — Andreas, Aug 10 '20 at 20:39
@Andreas tried setObject and localDate still got exception of invalid column type — Arhana, Aug 11 '20 at 04:30

Arvind Kumar Avinash · Answer 1 · 2020-08-10T20:10:41.307

2

Update

Posting this update since OP seems to be struggling with how to use the original solution.

Dear OP,

Java instantiates date/time/date-time in just one way and then you can format it in your custom way. The database works the same way. So, it doesn't matter what format you display to the user or in what format the user enters the date; once you parse it into date/time/date-time object by applying the corresponding format, you just pass it to Java/DB and Java/DB will take care of the rest.

import java.time.LocalDate;
import java.util.Scanner;

import com.mysql.jdbc.CallableStatement;

public class Main {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("Enter report date in MM-dd-yyyy format: ");
        String strDate = scanner.nextLine();
        LocalDate localDate = LocalDate.parse(strDate, DateTimeFormatter.ofPattern("MM-dd-yyyy"));
        CallableStatement st = conn.prepareCall("{call Report.pStoreDate(?,?)}");
        st.setObject(1, localDate);
        st.registerOutParameter(2, Types.REF_CURSOR);
        st.execute();
    }
}

Original answer:

I suggest you do not use the outdated and error-prone java.util.Date. Use LocalDate instead as shown below:

LocalDate localDate = LocalDate.now();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, localDate);
st.executeUpdate();

edited Aug 10 '20 at 20:10

answered Aug 09 '20 at 19:07

Arvind Kumar Avinash

71,965
6
74
110

after trying your solution I got java.sql.Exception : Invalid column type – Arhana Aug 10 '20 at 03:56
2

This answer has the right idea but seems to be missing the fact that the OP is using a `CallableStatement`, not a `PreparedStatement` (which only became clear in the comments long after this answer was posted). – Ole V.V. Aug 10 '20 at 06:44
@Arhana We may be able to help you with *Invalid column type* if you edit your question and give us the definition of your procedure with datatypes of the parameters and also the Java code where you try to apply the solution from this answer. – Ole V.V. Aug 10 '20 at 06:46
@Ole V.V, PROCEDURE pStoreData(d_sumDttm IN DATE, i_Retval out number); Short Code: System.out.print("Enter report date:"); String sdate = scanner.nextLine(); final Date date = new Date(); final SimpleDateFormat format = new SimpleDateFormat(); format.applyPattern(dd-MMM-yy); final String sysdt = format.format(date); java.sql.Date sqldt = java.sql.Date.valueOf(sysdt); callablestate = connection.prepareCall("{call Report.pStoreDate(?,?)}"); callablestate.setDate(1,sqldt); callablestate.registerOutParameter(2,Types.REF_CURSOR); callable.execute(); – Arhana Aug 10 '20 at 19:36
Exception is : java.sql.SQLException: Invalid column type – Arhana Aug 10 '20 at 19:37
@Arhana I said *if you edit your question* and I meant that. If we’re to help you, even with a question that has been closed (for good reasons that you may not yet have understood), this is not too much to ask. Code in comments is not readable. – Ole V.V. Aug 11 '20 at 03:41
Procedure: PROCEDURE pStoreData(d_sumDttm IN DATE, i_Retval out number); – Arhana Aug 11 '20 at 04:21
Weird. Haven’t you got an [edit](https://stackoverflow.com/posts/63330240/edit) link right there under the question?? Anyway I edited for you this time. – Ole V.V. Aug 11 '20 at 04:24
Short Code: System.out.print("Enter report date:"); String sdate = scanner.nextLine(); final Date date = new Date(); final SimpleDateFormat format = new SimpleDateFormat(); format.applyPattern(dd-MMM-yy); final String sysdt = format.format(date); java.sql.Date sqldt = java.sql.Date.valueOf(sysdt); callablestate = connection.prepareCall("{call Report.pStoreDate(?,?)}"); callablestate.setDate(1,sqldt); callablestate.registerOutParameter(2,Types.REF_CURSOR); callable.execute(); – Arhana Aug 11 '20 at 04:25
Exception is : java.sql.SQLException: Invalid column type – Arhana Aug 11 '20 at 04:26
@Arvind tried setObject but got exception Exception is : java.sql.SQLException: Invalid column type – Arhana Aug 11 '20 at 04:28
@Ole V.V i appreciate your help. – Arhana Aug 11 '20 at 04:32

Convertion to java.sql.Date for dd-MMM-yy format

1 Answers1

Update

Original answer: