UCanAccess: data exception: invalid datetime format

[Dario Ferrante]

Good day and Happy new Year.
In order to deactivate the trainer in my program, is executed this code (in JAVA)

public int UpdateTrainerDismissed(int codeTrainer, java.sql.Date date){
    int res=-1;
    try{
        Statement  s = c.createStatement();
        String query="";
        query += " UPDATE Trainers";
        query += " SET active=0, endAssignment='"+date+"'";
        query += " WHERE idTrainer="+codeTrainer;
        res = s.executeUpdate(query);
        s.close();
    }
    catch(SQLException e) {
        System.err.println(e);
    }
    return res; 
}

But it goes in Exception
net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid datetime format

The query executed is this:
UPDATE Trainers SET active=0, endAssignment='2015-01-04' WHERE idTrainer=24

Maybe UCanAccess does not support "java.sql.Date"?
Can I use only java.util.Date?

Thank you.

Dario.

[Marco Amadei]

Hi Dario,
UCanAccess supports the Access date formats and the # delimiter, so your sql statement is wrong.
You have to replace it with this:

UPDATE Trainers SET active=0, endAssignment=#2015-01-04# WHERE idTrainer=24

Cheers Marco

[Dario Ferrante]

I will try and let you know.
Thank you.

Dario.

[Dario Ferrante]

Perfect, I tried and the system is working correctly.
Thanks Marco.

Greetings.

[Apparao Tata]

Hi,
In my class i am declaring date like these

pre_date="2017-04-01 7:06:27 AM" .

output is : '#2017-04-01 7:06 AM#'

and it showing
But it goes in Exception
net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid datetime format

can any one give me solution for these.

[Gord Thompson]

Sorry, but I do not understand what you are asking. Please provide a Minimal, Complete, and Verifiable Example that clearly illustrates the problem you are having.

[Apparao Tata]

k i will give clear explanation thank you for your reply

[Apparao Tata]

Actually, I want to get the date formate below mention code:

String pre_date="2017-04-01 7:06:27 AM";
// String SCHOOL_CODE="DC-EMP";
// System.out.println(pre_date+"//"+SCHOOL_CODE);
StringTokenizer st = new StringTokenizer(pre_date, " ");
String predate = st.nextToken();
String pretime = st.nextToken();

    String md[]=pretime.split(":");
    String mdhh=md[0];
    String mdmt=md[1];
    String mdhm=mdhh+":"+mdmt;

    String ds[]=dateString.split("-");
    String fdd=ds[0];
    String fmm=ds[1];
    String fyy=ds[2];
    String fhh=ds[3];
    String fmt=ds[4];

    String fhm=fhh+":"+fmt;
    String fadmy=fyy+"-"+fmm+"-"+fdd;

    String endTime=predate+" "+mdhm;
    String startTime=fadmy+" "+fhm;
   //System.out.println(pre_date+"//"+SCHOOL_CODE);

//if(startTime.compareTo(endTime) > 0)
//{ 
    StringTokenizer stz = new StringTokenizer(endTime, "  ");
    String edat = stz.nextToken(); 
    String etim = stz.nextToken();
    StringTokenizer stz1 = new StringTokenizer(etim, ":");
    String time = stz1.nextToken();

int time1=Integer.parseInt(time.toString());
if(time1>12)
{
    endTime="#"+endTime+" PM#";
}
else
{
    endTime="#"+endTime+" AM#";
}

// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

//String accessFileNames = "D:/Program Files/eSSL/eTimeTrackLite/eTimeTrackLite1";

//System.out.println("accessFileNames="+accessFileNames);
//String databases = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+accessFileNames+".mdb;";

//Connection conn = DriverManager.getConnection(databases, "", "");

// Connection conn = DriverManager.getConnection("jdbc:ucanaccess://C:Program Files/eTimeTrackLite1.accdb;","","");
Connection conn = DriverManager.getConnection("jdbc:ucanaccess://D:/Program Files/eSSL/eTimeTrackLite/eTimeTrackLite1.accdb;","","");

java.sql.Statement ss = conn.createStatement();

String selTables = "SELECT userid,logDate,c1 FROM  Devicelogs where logDate >= ('"+endTime+"') order by logdate asc ";

//String selTables = "SELECT userid,logDate,c1 FROM  Devicelogs  ";
//System.out.println("SELECT userid,logDate,c1 FROM  Devicelogs where logDate >= ('"+endTime+"') order by logdate asc ");
//System.out.println("selTables1--------------->"+conn+"<br>"+endTime);
    //ss.execute(selTables);
    ss.executeQuery(selTables);

    ResultSet rss = ss.getResultSet();
    while((rss!=null) && (rss.next()))
    {
     eno.add(rss.getString(1));
     loginout.add(rss.getString(2));
     logtype.add(rss.getString(3)); 
    }

output: Error:: net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid datetime format.

Canu u give me solution for this.

[Gord Thompson]

You are making things much more complicated than they need to be. You can parse the date/time string with SimpleDateFormat

java.text.SimpleDateFormat sdfForParsing = 
        new java.text.SimpleDateFormat("yyyy-MM-dd h:mm:ss aa");

java.util.Date theDate = sdfForParsing.parse(pre_date);

 
and then use a PreparedStatement to run the query

String selTables = 
        "SELECT userid,logDate,c1 " +
        "FROM  Devicelogs " +
        "where logDate >= ? " +
        "order by logdate asc ";
PreparedStatement ps = conn.prepareStatement(selTables);
ps.setTimestamp(1, new java.sql.Timestamp(theDate.getTime()));
ResultSet rss = ps.executeQuery();

 
That way you don't have to worry about creating a specific format for the query parameter.

[Apparao Tata]

if u dont mine can i send entire code to u to rectify problem

[Syed Muhammad Hassan Zaidi]

So what I'm trying to is to get the user to input a date using Jdatechooser in eclipse, I then need to input this selected date in my database. I keep getting the error of invalid date time format.
This is my code :

try {

                Connection conn=DriverManager.getConnection("jdbc:ucanaccess://E:\\testing.accdb");
                String sql="insert into Homework (Description,Subject_ID,Name,Due_Date) values (?,?,?,?) ";
                PreparedStatement pst=conn.prepareStatement(sql);

                pst.setString(1, textFieldDes.getText());

                pst.setString(2, textFieldID.getText());

                pst.setString(3, textFieldName.getText());

               SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy h:mm:ss aa");
                String date= sdf.format(dateChooser.getDate());
                //java.util.Date theDate = sdf.parse();
               // pst.setTimestamp(4, new java.sql.Timestamp(theDate.getTime()));
                pst.setString(4,date);
                pst.executeUpdate();
                JOptionPane.showMessageDialog(null, "Data Saved");
                pst.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

[Gord Thompson]

Answered on Stack Overflow here.