CODEWITHSUNDEEP
javadatabasedatetimems-accessdatetime-format
wenzel267
wenzel267

Reputation: 93

How do I insert an empty DateTime to a MS Access database?

My problem is that I don't know how to handle an empty LocalDate object.

I have an Excel sheet which has a date column. The format is like this:

19693112 or 00000000 (if the date is not known yet)

Inside my java application I change the format of the data and upload it to a database.

I first convert the String to the format I need (which is 31.12.1969).

I'm able to insert the first format into my database without problems. but the second one never works because my conversion class converts it to 00.00.0000 and this is not accepted by MS Access.

I already tried to set the value I want to "null" but the database can't handle this value for a date field.

Is there a way that the value inside the database displays just an empty date field?

Thanks for any help.

public static void insertNewPensioners(List<Pensioner> newPensioners) {

    if (cell.getColumnIndex() == indexe.get(19)) { //checks if correct excelcell is used
        String cellValue = cell.getStringCellValue();
        if (cellValue.matches("0+")) {
            resignationDate = null;
        } else {
            resignationDate = DateFormatter.formatDate(cellValue);
        }

        pensionerExcel.add(new Pensioner(pknr, idkz, resignationDate));

        insertNewPensioners(newPensioners);
        
        public static void insertNewPensioners (List < Pensioner > newPensioners) {
            try {
                connect(pathDB);
            } catch (Exception e) {
                Alerts.connectionError();
                e.printStackTrace();
            }

            for (int i = 0; i < newPensioners.size(); i++) {
                String insert = ("Insert into Pensionär (PKNR, IDKZ, ResignationDate) VALUES " +
                        "(?,?,?)");
                try {
                    ps = connection.prepareStatement(insert);
                    ps.setInt(1, newPensioners.get(i).getPensionInsuranceNumber());
                    ps.setInt(2, newPensioners.get(i).getIdkz());
                    ps.setDate(3, Date.valueOf(newPensioners.get(i).getResignationDate()));
                    ps.executeUpdate();
                    validRows++;
                } catch (Exception e) {
                    Alerts.wrongValues();
                    e.printStackTrace();
                }
            }
            close();
        }

Upvotes: 1

Views: 1830

Answers (1)

Gustav
Gustav

Reputation: 55921

null would give a nullpointer exception again. Also he checks for an empy string but what I want is to "convert" 00000000 to an empty LocalDate. I need to know how I can get a format which can be used in my ms access database.

But the fact is - as mentioned - that "an empty date" in an Access table is Null - there is no other option.

So:

  • modify your code to insert Null for your empty dates
  • adjust your code to expect and accept Null for an empty date and convert this to whatever your code accepts (00000000).

Upvotes: 1

Related Questions