CODEWITHSUNDEEP
javasqlms-access
Newbie
Newbie

Reputation: 1724

Insert DateTime into MS Access

How to convert DateTime in java to the format that match with MS Access database?
This is how I format the date:

DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date date = new Date();
String stringDate = dateFormat.format(date);
System.out.println(stringDate);

Then this is my SQL query:

String sql = "INSERT INTO tblBorrows(DateIn) VALUES (" + dateFormat.parse(stringDate) + ")";

I print out the stringDate, it gives me 2016/03/29 16:42:24, but for my sql it is INSERT INTO tblBorrows(DateIn) VALUES (Tue Mar 29 16:42:24 SGT 2016)

This is my exception message: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.4 unexpected token: MAR required: )

Inside my MS Access, I select General Date as my Date/Time format: enter image description here

Upvotes: 1

Views: 3026

Answers (2)

user987339
user987339

Reputation: 10717

Change:

String sql = "INSERT INTO tblBorrows(DateIn) VALUES (" + dateFormat.parse(stringDate) + ")";

to 

String sql = "INSERT INTO tblBorrows(DateIn) VALUES (#" stringDate + "#)";

Maybe you should try to do database manipulation with prepared statements:

import java.sql.*;

public class jdbcMsAccess {

    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection(
                    "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};" + 
                    "Dbq=C:\\__tmp\\Database1.accdb;");
            Date date = new Date();
            PreparedStatement s = conn.prepareStatement(
                    "INSERT INTO tblBorrows(DateIn) VALUES (?)");
            s.setDate(1, new java.sql.Date(date.getTime()));
            s.execute();
            s.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }

    }
}

Upvotes: 1

Gustav
Gustav

Reputation: 55921

Use the correct delimiters for datetime in Access:

String sql = "INSERT INTO tblBorrows(DateIn) VALUES (#" + stringDate + "#)";

Upvotes: 0

Related Questions