Error 22007 Derby

this post in threaded view ♦ ♦ | Report Content as Inappropriate ♦ ♦ Date - Timestamp format for inserts? Hi: I can't figure this out. I'm getting an error that says: 'The syntax of the string representation of a datetime value is incorrect.' I've tried 4 or 5 different formats. It doesn't seem to like anything. The only 2 fields that are Date fields are DateCreated and DateExpired. They're taken from a MySql DATETIME datatype but I'm not sure what they convert to in Derby. What's wrong with this statement? INSERT INTO users (RecordID,CTMCClientID,OrgName,Prefix,FName,MidInit,LName,Addr1,Addr2,City,S tate,Zip,Phone,CellPhone,AltPhone,Fax,Email,Login,Password,Status,AccessLeve lID,DateCreated,CreatedBy,DateExpired ) VALUES(1,NULL,'SoftLink Systems, Inc.',NULL,'Marl',NULL,'Atkins',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,'marl','marl',0,1,'2006-09-10-00',1,'2050-01-01-00' ) Marl K. Atkins Microsoft Certified Professional SoftLink Systems, Inc. (407) 388-1886 Bryan Pendleton Reply | Threaded Open this post in threaded view ♦ ♦ | Report Content as Inappropriate ♦ ♦ Re: Date - Timestamp format for inserts? > What's wrong with this statement? Although the doc in http://db.apache.org/derby/docs/dev/ref/rrefsqlj27620.htmlappears to say that the minutes and seconds portions of the timestamp value can be ommitted, the code does not appear to conform to that behavior. So instead of '2006-09-10-00', use '2006-09-10-00.00.00' or '2006-09-10 00:00:00'. Hopefully that will be a reasonable solution to your problem for now. From what I see by reading through SQLTimestamp.parseDateOrTimestamp(), the code intends for the minutes and seconds portions to be optional, but the implementation doesn't handle that, so my initial reaction is that the documentation is correct and this is a bug in the timestamp parser. What do others think? Is Derby supposed to accept '2006-09-10-00' as a valid timestamp value? The documentation i

here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only http://apache-database.10148.n7.nabble.com/Date-Timestamp-format-for-inserts-td99979.html takes a minute: Sign up Not able to insert DATE type record into derby up vote 1 down vote favorite I am having a tough time to figure out how I can insert DATE type record in to derby database. I have created a table with column type TIME_EXECUTED DATE NOT NULL. Below is the query public void addMetric(String provider, Date http://stackoverflow.com/questions/35346125/not-able-to-insert-date-type-record-into-derby date) { try { Statement stmt = conn.createStatement(); String inserTableSql = "INSERT INTO tableName" + "(PROVIDER,TIME_EXECUTED) VALUES" + "(?,?)"; PreparedStatement p=conn.prepareStatement(inserTableSql); p.setString(1, provider); p.setDate(9, date); ResultSet rs=p.executeQuery(); stmt.close(); } catch (SQLException ex) { ex.printStackTrace(); } } This is how I am passing date in the method private static java.sql.Date getCurrentDate() { java.util.Date today = new java.util.Date(); return new java.sql.Date(today.getTime()); } This is the error I am getting ERROR 22007: The syntax of the string representation of a date/time value is incorrect. Any help will be very helpful. Thanks java mysql derby share|improve this question asked Feb 11 at 17:30 user557657 75117 1 check p.setDate(9, date); Shouldn't that be 2 instead of 9 ? –Sachin Feb 11 at 17:33 Sorry my bad that was typo. it is actually 2. And same issue. –user557657 Feb 11 at 17:36 Did you try passing in a java.sql.Date? –Joachim Isaksson Feb 11 at 17:42 @JoachimIsaksson yes I did –user557657 Feb 11 at 17:49 @user557657 Ah, sorry, didn't see the latter code, only saw the unqualified D

of https://sbforge.org/jira/browse/NAS-1254 the string representation of a datetime value is incorrect.Agile Board ExportXMLWordPrintable Details Type: Bug Status: Closed Priority: Critical Resolution: Fixed Affects Version/s: 3.13 Fix Version/s: None error 22007 Component/s: Harvester server Labels: None Description If I try to search for a start date i Job status I get: dk.netarkivet.common.exceptions.IOFailure: SQL error asking for job status error 22007 derby list in database SQLException trace: SQL State:22007 Error Code:30000 java.sql.SQLDataException: The syntax of the string representation of a datetime value is incorrect. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown Source) at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source) at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source) at dk.netarkivet.harvester.datamodel.JobDBDAO.getStatusInfo(JobDBDAO.java:684) at dk.netarkivet.harvester.webinterface.HarvestStatus.getjobStatusList(HarvestStatus.java:158) at org.apache.jsp.Harveststatus_002dalljobs_jsp._jspService(org.apache.jsp.Harveststatus_002dalljobs_jsp:149) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:109) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:389) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:486) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:380) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182) at org.mortbay.jetty.handler.ContextHandler.ha