SQL0180 Timestamp insert doesnt work, syntax error

[Guest.Visitor]

I have a new table containing a native timestamp (Z) field. I used CREATE COLLECTION to make the library. Both ops navagator Run SQL Script and JDBC (toolbox) return the error when insert of field in form '2000-11-02 20:04:00' is attempted. The database function of ops navagator takes the entry fine. Access with a linked table also updates fine. I have turned on the trace function in JDBC. It shows date format is ISO. I also tried updating a separate file containing separate date and time fields. This works fine. Anyone have any idea what's wrong?

[Guest.Visitor]

Not real sure about the 400 but on S/390 the time stamp is ccyy-mm-dd-hh:ii:ss.ttttt Retrieve the 400 system timestamp and see what it looks like. bobh

[Guest.Visitor]

Edwin, Try adding something like .939328 so that you get a value that looks like: INSERT INTO FILEX VALUES('bla', 'bla', '2000-11-03-06.01.54.939328') If you are trying to just get the current time into the field, you could not specify the field like: INSERT INTO FILEX (field1, field2) VALUES('bla', 'bla') David MOrris

[Guest.Visitor]

Bob, I'm not sure I understand the term "400 system timestamp" in this context. I did a select statement against the same table. The resulting timestamp shows a space between the date and time. The time has 6 decimal places (ccyy-mm-dd hh.mm.ss.tttttt). Then I tried green-screen DSPPFM to display the contents there. It shows the dash between the date and time. I also tried to redo the insert using the dash but it still fails. BTW I am using V4R5 with the latest cum package on the host. Do you think this might be a bug?

[Guest.Visitor]

There is also a special register called "current timestamp". You could use it as <pre> create table table1 ( col1 integer, col2 timestamp); insert into table1 values( 1, current timestamp ); </pre> <a > href="http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rbafzms t50.htm#HDRSPECREG">click here for V4R4 SQL special registers</a> HTH Dan

[Guest.Visitor]

Edwin, <font color blue>Both ops navagator Run SQL Script and JDBC (toolbox) return the error when insert of field in form '2000-11-02 20:04:00' is attempted.</font> This is an example from the SQL reference:<pre> A string representation of a timestamp is a character string that starts with a digit and has a length of at least 16 characters. The complete string representation of a timestamp has the form yyyy-mm-dd-hh.mm.ss.nnnnnn or yyyymmddhhmmss. Trailing blanks can be included. Leading zeros can be omitted from the month, day, and hour part of the timestamp when using the timestamp form with separators. Trailing zeros can be truncated or omitted entirely from microseconds. If you choose to omit any digit of the microseconds portion, an implicit specification of 0 is assumed. Thus, 1990-3-2-8.30.00.10 is equivalent to 1990-03-02-08.30.00.100000</pre> So, it looks like you need the dash between the date and time and replace the colons with periods -or- drop all of the formatting. Bill

[Guest.Visitor]

Dan, Yes, the value CURRENT_TIMESTAMP is valid and does work in the application. Thanks for your help.

[Guest.Visitor]

David, That did it. The example you gave worked fine. Thanks again.

[Guest.Visitor]

Bill, Thanks for the reference to the SQL doc. I will be sure to consult it next time! The format mentioned is correct. The application now works fine.