3

I want to insert a datetime value to Access, but I get this error:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.4 data exception: invalid datetime format

Here is the code : 

private void txtsubmitActionPerformed(java.awt.event.ActionEvent evt) {                                          
    SimpleDateFormat A = new SimpleDateFormat("dd/MM/yyyy");
    Peminjaman P= new Peminjaman(setIDTrans(),txtid.getText(),A.format(txttglpinjam.getDate()),A.format(txttglkembali.getDate()),txtplat.getText(),txtnama.getText(),txtdriver.getText());
    c.InsertPeminjamanD(P);
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
janotama
  • 197
  • 7
  • 18
  • 1
    You might try using `#17/04/2004#` instead of `17/04/2004` as date string as shown at http://www.techonthenet.com/access/functions/date/format.php. If that solves your problem, report back, then I make my comment an answer for you to accept it. – Vampire Jun 05 '16 at 18:06

1 Answers1

4

The error message indicates that your InsertPeminjamanD method is ultimately trying to do something like

INSERT INTO Table1 (DateTimeField) VALUES ('31/05/2016')

and that won't work for two reasons:

  1. UCanAccess expects date literals to be enclosed in hash marks (#), and

  2. UCanAccess normally expects xx/yy/zzzz date literals to be MM/dd/yyyy, not dd/MM/yyyy.

So, the above query will work if it is rearranged as ...

INSERT INTO Table1 (DateTimeField) VALUES (#05/31/2016#)

... although it really would be better to use a PreparedStatement and pass it a proper date value:

String sql = "INSERT INTO Table1 (DateTimeField) VALUES (?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setDate(1, java.sql.Date.valueOf("2016-05-31"));
ps.executeUpdate();
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418