| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| TIMESTAMP dropping milliseconds |
« View previous topic :: View next topic » |
| Author |
Message
|
| mc01285 |
 Posted: Fri Oct 01, 2010 12:48 pm Post subject: TIMESTAMP dropping milliseconds |
 |
|
Novice
Joined: 11 May 2009
Posts: 11
|
I have an incoming field defined as xsd:dateTime and I need to pass it to an Oracle Stored Procedure as a TIMESTAMP.
The date coming in looks like 2010-10-01T11:10:08.304-04:00
I have tried various CAST's to get to what looks like the correct format in the debugger. My TIMESTAMP variable in the debugger looks like 2010-10-01 11:10:08.304
On the Oracle side the TIMESTAMP field is missing the milliseconds.
Any ideas?
Thanks for the help! |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Fri Oct 01, 2010 12:51 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
What is the definition of the field on the ORACLE side? Is it sufficient to contain milliseconds? Is it sufficient to contain 3 digits of precision in milliseconds?
What does the InfoCenter say on the conversion of TIMESTAMP data between ESQL and Oracle? |
|
| Back to top |
|
|
| mc01285 |
| Posted: Fri Oct 01, 2010 12:55 pm Post subject: |
|
|
Novice
Joined: 11 May 2009
Posts: 11
|
| The Oracle field is defined as TIMESTAMP(6). |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sat Oct 02, 2010 1:10 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I've seen this happen where there are no milliseconds in the value.
i.e.
17:24:35.000
you get
17.24.35 returned.
btw, this is also true if you are using the MQMD.PutTime and casting it to a char. You have to do a test to see if the length includes milliseconds. If not then you have to add them otherwise you can append it to the MQMD.PutDate and insert it into a db as a timestamp. Non the most elegant solution but one that has caused some problems in the past.
_________________
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 |
|
|
| lancelotlinc |
| Posted: Mon Oct 04, 2010 7:10 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
BTW - this is not a Message Broker issue, it is an Oracle issue. You may find better help in an Oracle support group.
Mods may like to lock this thread since it does not apply to Message Broker.
Oracle does not store millisecond granularity directly but it does store seconds with a fraction component which can help you to get the milliseconds. Timestamp precision default is platform dependent, on UNIX it defaults to 6 and on windows it defaults to 3. The valid range is [0-9].
The oracle.jbo.domain.Timestamp class accepts dates and times in the same format accepted by java.sql.TimeStamp (either a long milliseconds time value or the year, month, day, hour, minute, second, nano format) and java.sql.Timestamp (either a milliseconds time value or the year, month, day format).
On your select from table SQL statement, always use two FF's, regardless of the number of decimal digits you wish to see or use. 'FF' stands for the fractional seconds.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Mon Oct 04, 2010 7:47 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| 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
|
|
|
|
