SQL update uses wrong date format

[jay.l.fisher]

I have a SQL data source with a field of type="date". In the database table it is also a simple date column, not datetime. In my ListGrid it displays correctly as a simple date (4/23/2009) and also in a DynamicForm. However, in a DetailViewer is shows up with time as April 23, 2009 12:00:00 AM CDT and, more importantly, whenever the record is updated (either in the ListGrid or DynamicForm) the SQL UPDATE statement has the new value formatted as '2009-04-23 00:00:00.0' which the server rejects as being in the wrong format.

[Isomorphic]

What database and version, and what's the exact SQL column type? Ideally show the actual SQL used to create the table.

[jay.l.fisher]

It is a DB2 table on an IBM iSeries machine. The table wasn't created with SQL, but it is a DATE type column and other SQL tools that access it through JDBC recognize it as such. I've tried doing the UPDATE with the standard Eclipse SQL tools and it works as long as the new value does not include the time component. This is how I configured the server.properties.

sql.as400.database.type: db2
sql.as400.interface.type: driverManager
# DataSource
sql.as400.driver: com.ibm.as400.access.AS400JDBCDriver
sql.as400.driver.url: jdbc:as400:myHost;naming=system;prompt=false;user=me;password=mypassword

[Isomorphic]

Hi Jay,

Still looking at the best solution for this - it's very common to store a logical date value in a timestamp or datetime column so there may need to be the ability to configure the underlying sql type via the DataSource.

For now, one approach would be to add DMI logic or a custom DataSource subclass that goes through dsRequest.values before the SQL engine gets it, and changes any Date values to a String formatted as DB2 expects.

[jay.l.fisher]

It has been quite a while, and I think the solution to this was your addition of a defaultDateType setting in the sql configuration strings in server.properties.

But now we've run into a similar problem with TIME fields, again using iSeries DB2.

When the value in a form field is “01:00am”, in the server constructor the DSRequest values map shows …

Thu Jan 01 01 01:00:00 PST 1970

… which causes the following framework to attempt this insert.

INSERT INTO IPPEDHR (CARDTYPE, DEALDESC, DEALNBR, DEALSTAT, ENDDTE, ENDTIM, STARTDTE, STARTTIM, TACKON, TRANLVL) VALUES ('BBBB', 'VVVV', 21, 'I', '2013-04-23', '1970-01-01 04:00:00', '2013-04-23', '1970-01-01 01:00:00', 'Y', '1')

Which generates this error.

[SQL0180] Syntax of date, time, or timestamp value not valid.

The SQL engine is expecting HH.MM.SS format, not HH:MM:SS.

Is there a way to control the time format?

[Isomorphic]

iSeries DB2 certainly has some unique needs.

You can control the format used via sql.{dbName}.defaultDateFormat and defaultDateTimeFormat, which are format strings used with Java's SimpleDateFormat.

[dhensche]

I work with Jay and am following up.

When changing server.properties with either of ...

sql.{dbName}.defaultTimeFormat: HH.mm.ss
sql.{dbName}.defaultDateTimeFormat: HH.mm.ss

... (substituting for correct DB), the same INSERT statement and error are emitted.

As SimpleDateFormat class is mentioned, we took the formatting for the properties from the class JavaDoc. Is any additional coding necessary to connect the dots (for a .ds.xml type="time" field to have its correct SQL INSERT value generated)? Thanks.

[Isomorphic]

Should be "defaultDateFormat" to affect dates (you have defaultTimeFormat).

The other setting is correct, but would only have affected a field declared as type "datetime", which presumably is not the case for this INSERT.

[dhensche]

This post has actually devolved into a type="time" question as of the "23rd Apr 2013, 23:19" posting (Jay's original date question was answered). Excuse the transition (were trying to piggyback off a similar topic). What we have now is a TimeItem issue, reiterated ...

When the value in a form field is “01:00am”, in the server constructor the DSRequest values map shows …

Thu Jan 01 01 01:00:00 PST 1970

… which causes the following framework to attempt this insert.

INSERT INTO IPPEDHR (CARDTYPE, DEALDESC, DEALNBR, DEALSTAT, ENDDTE, ENDTIM, STARTDTE, STARTTIM, TACKON, TRANLVL) VALUES ('BBBB', 'VVVV', 21, 'I', '2013-04-23', '1970-01-01 04:00:00', '2013-04-23', '1970-01-01 01:00:00', 'Y', '1')

Which generates this error.

[SQL0180] Syntax of date, time, or timestamp value not valid.

The SQL engine is expecting HH.MM.SS format, not HH:MM:SS.

Is there a way to control the time format?

In the .ds.xml, the two fields for which the INSERT VALUES clause is incorrectly generated are ...

<field name="STARTTIM" title="Start Time" type="time" required="true"/>
<field name="ENDTIM" title="End Time" type="time" required="true"/>

... which end up having an SQL INSERT VALUES clause wind up with respectively ...
'1970-01-01 01:00:00', '1970-01-01 04:00:00'
... (errant values for the iSeries SQL engine) for client TimeItems showing ...
“01:00am”, "04:00am"
... in the browser. We would rather not have to handle this by pre-processing the values map in the server constructor, and thought you were inferring we could handle this from server.properties.

[Isomorphic]

We will look at implementing a defaultTimeFormat property. Which version of the product are you using?

[dhensche]

v8.3p_2013-03-13/PowerEdition Deployment (built 2013-03-13)

Much appreciated!

[Isomorphic]

The change has just been committed on the 8.3 branch. It will be present in nightly builds of 3.1p and 4.0d as of tomorrow (4/30). The new property "defaultTimeFormat" works exactly like the existing "defaultDateFormat" and "defaultDateTimeFormat" properties - just declare a "sql.{dbType}.defaultTimeFormat" property in your server.properties file, set to a Java SimpleDateFormat value.