| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| DB2 Timestamp |
« View previous topic :: View next topic » |
| Author |
Message
|
| bobbee |
 Posted: Sun Aug 07, 2016 3:36 am Post subject: DB2 Timestamp |
 |
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
I am calling a stored procedure (SP) I am also doing other DB2 calls all are using a timestamp format. This one stopped working.
Here is the IIB (9.0.0.4) code:
| Code: |
SET srcFormat = 'YYYY-MM-dd''T''HH:mm:SS';
SET InTimeStart = cast('2016-02-09T00:00:00' as TIMESTAMP FORMAT srcFormat);
SET InTimeEnd = cast('2016-02-09T23:59:59' as TIMESTAMP FORMAT srcFormat);
CALL loadTempTableProc1(InTimeStart, InTimeEnd);
|
Here is what it looks like in DEBUG before the call:
| Code: |
srcFormat:CHARACTER:YYYY-MM-dd'T'HH:mm:SS
InTimeStart:TIMESTAMP:java.util.GregorianCalendar
[time=1454994000000,areFieldsSet=true,areAllFieldsSet=false,lenient=true,zone=sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,
dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,
dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,
startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,
minimalDaysInFirstWeek=1,ERA=?,YEAR=2016,MONTH=1,WEEK_OF_YEAR=?,WEEK_OF_MONTH=?,DAY_OF_MONTH=9,DAY_OF_YEAR=?
,DAY_OF_WEEK=?,DAY_OF_WEEK_IN_MONTH=?,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=?
,DST_OFFSET=?]
(ie: 2016-02-09 00:00:00.000)
InTimeEnd:TIMESTAMP:java.util.GregorianCalendar[time=1455080340590,areFieldsSet=true,areAllFieldsSet=false,lenient=true,
zone=sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,
lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3
,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,
endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=?,YEAR=2016,MONTH=1,WEEK_OF_YEAR=?,
WEEK_OF_MONTH=?,DAY_OF_MONTH=9,DAY_OF_YEAR=?,DAY_OF_WEEK=?,DAY_OF_WEEK_IN_MONTH=?,AM_PM=1,HOUR=11,HOUR_OF_DAY=23,
MINUTE=59,SECOND=0,MILLISECOND=590,ZONE_OFFSET=?,DST_OFFSET=?]
(ie 2016-02-09 23:59:00.590)
|
This is the error message:
| Code: |
Text:CHARACTER:[IBM][CLI Driver][DB2/LINUXX8664] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
|
Any suggestions? |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Sun Aug 07, 2016 11:22 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
One thing that is suspicious is that you don't have an ESQL TIMESTAMP but a java.util.GregorianCalendar...where I would have expected a java.sql.Timestamp. So the character representation of this date time is the default java representation unless you format it differently. And that default representation surely does not match the DB representation (char) of a timestamp....
When using a DB timestamp I've found it to be advantageous to pass the Character representation of the timestamp instead of a java.sql.timestamp format... (loss of precision in hundreds of seconds when using a sql timestamp format and other format problems... come to mind.)
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Aug 07, 2016 11:38 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| fjb_saper wrote: |
When using a DB timestamp I've found it to be advantageous to pass the Character representation of the timestamp instead of a java.sql.timestamp format... (loss of precision in hundreds of seconds when using a sql timestamp format and other format problems... come to mind.)
|
I've always passed a CHAR type when using Timestamps in DB2 Databases
Not all the DB types supported by IIB like the same datatype when it comes to Timestamps.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| bobbee |
| Posted: Sun Aug 07, 2016 12:33 pm Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
| Originally we had three parameters T1, T2 and CommitCount. They ased if we could removed the commit count. I actually never asked for it. So they removed it. then was getting deadlock, without telling me, they put the third parm back. For some reason the thrid parm missing from my call was generating that message I just happened to look at the SP and said, who put that third parm back. So I put it and error went away. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
