How to Convert String type date to date for entry into oracle DB - Java

Question

Incoming date format id 2014-11-03 00.00.00.0 and need to be converted to either 03/11/2014 or 03-Nov-2014.

What could be the best possible and smallest code?

Why convert it to a string at all and not just a java.sql.Date instance? — Mureinik
– Mureinik, Commented Oct 4, 2016 at 16:22
It would be better if you were using ORM, or even prepared statements where you can set a date object directly. Anyway, an input date that is coming as a string from a third party can be parsed as a date using a SimpleDateFormat with format 'yyyy-MM-dd hh.mm.ss.t' — sanastasiadis
– sanastasiadis, Commented Oct 4, 2016 at 16:32
@Mureinik Actually, I am setting the date from the DB to a JSP form and then again retrieving it to set it in another table and while supplying the date as "2014-11-03 00.00.00.0", DB restricts it — Ekas Preet Singh
– Ekas Preet Singh, Commented Oct 4, 2016 at 17:22

Community · Accepted Answer · 2020-07-31 17:55:22Z

OK, so you have a String with the value 2014-11-03 00.00.00.0 and you want to insert that value in a DATE field inside a Oracle Database Table, using Java's JDBC?

Well, first, convert that String into a Date object using a SimpleDateFormat object:

String s = "2014-11-03 00.00.00.0";
Date d = new SimpleDateFormat("yyyy-MM-dd HH.mm.ss.S").parse(s);

Then, if you are using plain statements, reformat it the way Oracle would accept it in an SQL INSERT command, using again a SimpleDateFormat object:

String sd = new SimpleDateFormat("dd/MM/yyyy").format(d);

and insert it into your Statement:

cn.createStatement().executeUpdate("INSERT INTO TABLE(...) VALUES(" + sd + ");

I would recommend you to better use a PreparedStatement instead of a plain SQL INSERT statement so your statement is sanitized:

PreparedStatement ps = cn.prepareStatement("INSERT INTO table VALUES(?, ?, ?)");

And then, insert your date as a Date object without the need of formatting into a String:

ps.setDate([index of the field to insert], d);

Joop Eggen · Accepted Answer · 2016-10-04 16:41:24Z

1

If you're using java 8 there's a very useful date format class java.time.format.DateTimeFormatter With that class you can do something like these:

LocalDate date = LocalDate.now();
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH.mm.ss");
String text = date.format(formatter);
LocalDate parsedDate = LocalDate.parse(text, formatter);

DateTimeFormatter formatter2 = DateTimeFormatter.ofPattern("MM/DD/YYYY");
String newText = parsedDate.format(formatter2);

edited Oct 4, 2016 at 16:41

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

answered Oct 4, 2016 at 16:32

seiya

1,5463 gold badges19 silver badges29 bronze badges

Comments

Wesley Heron · Accepted Answer · 2016-10-04 16:23:52Z

0

Try this code:

Date d = new SimpleDateFormat("dd/MM/yyyy").parse(date);

answered Oct 4, 2016 at 16:23

Wesley Heron

4233 gold badges13 silver badges27 bronze badges

Comments

Joop Eggen · Accepted Answer · 2016-10-04 16:40:09Z

0

In java 8 getting a java.sql.Date (= java.util.Date with zero time) from that string may use a formatter, but as the string is almost ISO standard:

    LocalDateTime x = LocalDateTime.parse("2014-11-03T00:00:00.0");

one could patch the string as:

    LocalDateTime t = LocalDateTime.parse("2014-11-03 00.00.00.0"
            .replaceFirst(" ", "T") // Make ISO
            .replaceFirst("\\.", ":")
            .replaceFirst("\\.", ":"));

    java.sql.Date time = java.sql.Date.valueOf(t.toLocalDate());

Or

    LocalDate t = LocalDate.parse("2014-11-03 00.00.00.0"
            .replaceFirst(" .*$", "")); // Remove time part
    java.sql.Date time = java.sql.Date.valueOf(t);

answered Oct 4, 2016 at 16:40

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Comments

Basil Bourque · Accepted Answer · 2016-10-05 04:27:06Z

tl;dr

myPreparedStatement.setObject( 
    … , 
    LocalDate.parse(  "2014-11-03 00.00.00.0".substring( 0 , 10 ) ) 
);

What could be the best possible and smallest code?

There you go. But I do not recommend actually crunching so much into a single line. The “best” code is rarely the “smallest” code.

Details

Other answers are correct. But here is something shorter.

First of all, you should not be submitting a date-only value to a database as a string. Submit as a date-only object. Your JDBC driver will handle the details of communicating that to the database.

First extract your date value by calling String::substring. With padding zeros on month and day-of-month we know the first 10 digits are the date.

LocalDate ld = LocalDate.parse(  "2014-11-03 00.00.00.0".substring( 0 , 10 ) );

If your JDBC driver complies with JDBC 4.2 or later, you can pass the LocalDate directly via PreparedStatement::setObject.

myPreparedStatement.setObject( … , ld );

…or perhaps:

myPreparedStatement.setObject( … , ld , JDBCType.DATE );

If your driver does not comply, convert to the legacy class java.sql.Date. Look to new methods added to the old classes.

java.sql.Date sqlDate = java.sql.Date.valueOf( ld );

The approach described here assumes the input string was intended for UTC. If not, you need to adjust into UTC. Search Stack Overflow for posts on parsing a string as a ZonedDateTime.

Winter MC · Accepted Answer · 2019-12-19 10:18:45Z

This also works for me. a small utility method to generate a string value of a date that can be inserted into an Oracle Database Date Field, abit dirty but works. first function uses Calender class and second one LocalDate ( Java8 or higher )

1.

      /*
     * Format Date for Presentation in format
     * 19-DEC-2019   i.e. Oracle DB DD-MMM-YYYYY
     */
    private String stringToDate(String inputDate) {
        String finalDate = null;
        if (inputDate == null)
            return null;

        try {
            Date parseDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(inputDate);

            // set your local time zone
            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("Africa/Lusaka"));
            cal.setTime(parseDate);
            String finalMonth = "";
            int yearVal = cal.get(Calendar.YEAR);
            int monthVal = cal.get(Calendar.MONTH);  //returns value 0 to 11
            int dayVal = cal.get(Calendar.DAY_OF_MONTH);

            switch(monthVal) {
            case 0:
                finalMonth = "JAN";
                break;
            case 1:
                finalMonth = "FEB";
                break;
            case 2:
                finalMonth = "MAR";
                break;
            case 3:
                finalMonth = "APR";
                break;
            case 4:
                finalMonth = "MAY";
                break;
            case 5:
                finalMonth = "JUN";
                break;
            case 6:
                finalMonth = "JUL";
                break;
            case 7:
                finalMonth = "AUG";
                break;
            case 8:
                finalMonth = "SEP";
                break;
            case 9:
                finalMonth = "OCT";
                break;
            case 10:
                finalMonth = "NOV";
                break;
            case 11:
                finalMonth = "DEC";
                break;

            }

            finalDate = dayVal+"-"+finalMonth+"-"+yearVal;

        } catch (ParseException e) {
            e.printStackTrace();
        }

        return finalDate;
    }

2.

       /*
     * Format Date for Presentation in format
     *  19-DEC-2019   i.e. Oracle DB DD-MMM-YYYYY
     */
    private String stringToDateJava8(String inputDate) {
        String finalDate = null;
        if (inputDate == null)
            return null;

        try {
            Date parseDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(inputDate);

            String finalMonth = "";
            LocalDate localDate = parseDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            int yearVal  = localDate.getYear();
            int monthVal = localDate.getMonthValue();   //returns value 1 to 12
            int dayVal   = localDate.getDayOfMonth();

            switch(monthVal) {
            case 1:
                finalMonth = "JAN";
                break;
            case 2:
                finalMonth = "FEB";
                break;
            case 3:
                finalMonth = "MAR";
                break;
            case 4:
                finalMonth = "APR";
                break;
            case 5:
                finalMonth = "MAY";
                break;
            case 6:
                finalMonth = "JUN";
                break;
            case 7:
                finalMonth = "JUL";
                break;
            case 8:
                finalMonth = "AUG";
                break;
            case 9:
                finalMonth = "SEP";
                break;
            case 10:
                finalMonth = "OCT";
                break;
            case 11:
                finalMonth = "NOV";
                break;
            case 12:
                finalMonth = "DEC";
                break;

            }

            finalDate = dayVal+"-"+finalMonth+"-"+yearVal;

        } catch (ParseException e) {
            e.printStackTrace();
        }

        return finalDate;
    }

Collectives™ on Stack Overflow

How to Convert String type date to date for entry into oracle DB - Java

6 Answers 6

Comments

Comments

Comments

Comments

tl;dr

Details

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

tl;dr

Details

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related