How to change format of mm-dd-yyyy(of data from csv file) to be stored as yyyy-mm-dd in MySql using Java

Question

The data exists in a csv file. I am using a CSVReader to read each row and then store the corresponding values in the database. so while reading the strings I split the date string using "-"(date format is like 01-02-1997)and store in an array and then swap the three elements of the array and store it back in the db. Is there any better way.

Sample Row data in csv file:

10001   MICHELLE    VILLEGAS3   Savings SRINATH MICHELLE.VILLEGAS3@NOBODY.COM   Y   10-7-18 1050 WEST FIFTH STREET      AZUSA   IND 917023308

it is comma separated only.

private static void readCsv(String tableName) throws FileNotFoundException, IOException, SQLException {
    CSVReader csvreader = null;

    try{
        Reader reader = Files.newBufferedReader(Paths.get(filePath));
        csvreader = new CSVReaderBuilder(reader).withSkipLines(1).build();
        sql = "insert into ? values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        ManageDBResource.createConnectionToDB();
        pstmt = ManageDBResource.conn.prepareStatement(sql);
        pstmt.setString(1, tableName);

        String[] rowData = null;

        while((rowData = csvreader.readNext()) != null) {

            int i = 2;

            for (String data : rowData) {

                if(i == 8 && data != null && rowData[12] != "IND") {
                    String[] date = data.split("-");
                    String temp = date[0];
                    date[0] = date[1];
                    date[1] = temp;
                }
                pstmt.setString(i++,data);
                //System.out.print(data + " ");
            }
            //System.out.println();
        }
        int result = pstmt.executeUpdate();
        if(result == 1) {
            System.out.println("Data loaded Successfully.");

        }
    }
    finally {
        pstmt.close();
        csvreader.close();
    }

How exactly do I use the STR_TO_DATE() function in the java program. sorry I am new to programming thanks for helping.

Side note - dont use `!=` to compare Strings; don't use String as data type for everything, use proper SQL types for numbers and dates. — Gyro Gearless, Jul 18 '18 at 11:33

Halko Karr-Sajtarevic · Accepted Answer · 2018-07-19T05:02:43.660

3

Why don't you use java's SimpleDateFormat?

e.g.

java.text.SimpleDateFormat source = new java.text.SimpleDateFormat("MM-dd-yyyy");
java.util.Date date = source.parse(data);
java.text.SimpleDateFormat target = new java.text.SimpleDateFormat("yyyy-MM-dd");
data = target.format(date);

or better: The modern java.time API

DateTimeFormatter source = DateTimeFormatter.ofPattern("MM-dd-yyyy");
LocalDate date = LocalDate.parse(data, source);
DateTimeFormatter target = DateTimeFormatter.ofPattern("yyyy-MM-dd");
data = date.format(target);

edited Jul 19 '18 at 05:02

answered Jul 18 '18 at 10:52

Halko Karr-Sajtarevic

2,248
1
16
14

1

Please don’t teach the young ones to use the long outdated and notoriously troublesome `SimpleDateFormat` class. At least not as the first option. And not without any reservation. Today we have so much better in [`java.time`, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/) and its `DateTimeFormatter`. – Ole V.V. Jul 18 '18 at 15:24

score 1 · Answer 2 · answered Jul 18 '18 at 10:59

It's quite simple. Start with this question and convert your column data (String) to java.sql.date. Now use setDate method of prepared statement to pass the date to your insert query:

pstmt.setDate(i++, dateObject);

Don't worry about how mysql will store it. You can always query the DB to get date values in your required format. The tools such as SQL Developer etc. you use can be configured to display date in specific format.

Max · Answer 3 · 2018-07-23T08:45:19.323

1

You can use

final DateTimeFormatter dateFormat = DateTimeFormatter.ofPattern("MM-dd-yyyy");
LocalDate localDate = LocalDate.parse("01-20-2018", dateFormat);
pstmt.setDate(i, java.sql.Date.valueOf(localDate));

If parsing fails the parse method will throw DateTimeParseException.

So you can

LocalDate localDate = null;
try {
     localDate = LocalDate.parse("01-20-2018", dateTimeFormat);
} catch(DateTimeParseException e) {
     e.printStackTrace();
     //some action that should be taken in case it fails
}

edited Jul 23 '18 at 08:45

answered Jul 18 '18 at 10:59

Max

1,536
1
14
18

The LocalDateTime was a bit of an oversight. Does setObject work for every object in the newer JDBC? (I mean for the standard once you can expect to have some sort of SQL equivalent) It´s nice, but for me, it mostly does not matter, because most of the time I use JDBC it´s with SQLite and then there aren´t really a whole lot of types anyways. – Max Jul 18 '18 at 17:07
This is the best answer. Assuming you can use JDBC 4.2 or higher there’s no need to convert to the outdated `java.sql.Date` type, just use `pstmt.setObject(i, localDate));`. – Ole V.V. Jul 18 '18 at 17:40

How to change format of mm-dd-yyyy(of data from csv file) to be stored as yyyy-mm-dd in MySql using Java

3 Answers3