java.sql.BatchUpdateException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

Question

I am trying to do batch insert to SQL Server where I am getting the column values from a csv file.

This is how am handling each row of the csv file, but I am getting either

nvarchar cannot converted to numeric

- or -

dateTime exception

---

The code:

 for (int i = 0; i < count; i++) {
            Object val = columnVal[i];
            if (StringUtils.isEmptyOrWhitespaceOnly(val.toString())) {
                System.out.println(val);
                ps.setObject(i + 1, null);
            } else {
                System.out.println(val);
                ps.setObject(i + 1, val);
            }

        }

Answer 1

When the SQL Server data type is DATETIME, DATETIME2, DATE etc, use a one of the following data types when setting the object's value:

• java.sql.date: you only need a date part
• java.sql.timestamp: you need date + time part
• java.time classes for Java 1.8+ and you are using a JDBC driver compliant with JDBC 4.2 or later; e.g. java.time.LocalDateTime.

You can gain more insights from this question on Stack Overflow.

---

Edit:

I am trying to write a generic insert since I don't know what kind of value is coming from csv file i.e., why I used object array to get values

In that case you should be asking that the CSV use date/datetime values that are formatted in ISO 8601 format. SQL Server cannot convert any of the permutations of datetime formats. That would be impossible especially since datetime formats in the USA are mm/dd/yyyy and those in most other countries typically dd/mm/yyyy.

You could get away with setting an explicit date format using T-SQL SET DATEFORMAT, but you would need to know what the format is up front and SQL Server would have to support that format as well.

---

Edit 2:

Actually i tried with date like dd-mm-yyyy format am able to batch insert .am facing problem when i tried the datetime format as dd-mm-yyyy hh.mm.ss . is there any way to handle this

For that first format, it could easily break if the locale changes... it could then interpret as mm-dd-yyyy just as easily.

Remember my advice: always use ISO8061 format when serializing datetimes. You need to use YYYY-MM-DDThh:mm:ss[.mmm] (the T is a literal T character) or YYYYMMDD[ hh:mm:ss[.mmm]]. See DATETIME - Supported String Literal Formats for datetime.