| Author |
Message
|
| Sai K |
 Posted: Sun Jul 31, 2011 6:13 pm Post subject: Inserting the timestamp value in oracle database from WMB. |
 |
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
Hi All,
I am using WMB 7.0.0.2 and trying to insert timestamp (2011-06-16T11:25:00) value into oracle database. Oracle column has the datatype as "date". When I am passing the same value(2011-06-16T11:25:00), it was inserted as 2011-06-16.
Please advice.. Thanks in advance. |
|
| Back to top |
|
 |
| wils4mic |
| Posted: Sun Jul 31, 2011 9:35 pm Post subject: |
|
|
Newbie
Joined: 16 Nov 2010
Posts: 3
|
| I had that as well. Turned out the target field was defined 'DATE' which is date only; time information is dropped. I asked my friendly DBA to make the field 'TIMESTAMP(6)' and got date and time with milliseconds as required. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Mon Aug 01, 2011 1:45 am Post subject: Re: Inserting the timestamp value in oracle database |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| wils4mic wrote: |
| Turned out the target field was defined 'DATE' which is date only; time information is dropped. |
The Oracle DATE data type also includes hours, minutes, and seconds, but may require an explicit FORMAT string to use them. |
|
| Back to top |
|
|
| Sai K |
| Posted: Mon Aug 01, 2011 6:26 am Post subject: |
|
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
Is there any way that we can handle from broker with out changing datatype of the column in database ?  |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Aug 01, 2011 6:48 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Sai K wrote: |
| Is there any way that we can handle from broker with out changing datatype of the column in database ? |
Well I've not tried it, but I'd go with rekarm01 on this one; format the string so Oracle noticies the additional informaton.
(I don't have an Oracle database to hand, so I'm going on faith that the DATE column type includes time information)
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| WGerstma |
| Posted: Mon Aug 01, 2011 8:56 am Post subject: |
|
|
Acolyte
Joined: 18 Jul 2011
Posts: 55
|
| Trust my, It has time information. When you compare two Oracle Dates, the comparision will be done including the time. However standard SQL tools and other external are by default only delivered with the date aspects, unless you explicitly force the retrieval of hours and minutes via the FORMAT. |
|
| Back to top |
|
|
| Sai K |
| Posted: Mon Aug 01, 2011 12:49 pm Post subject: |
|
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
| I passed the input value to the function and retrived the hours, minutes and seconds with FORMAT. When I am passing that value to database it is inserted as only date. time format is dropped. |
|
| Back to top |
|
|
| WGerstma |
| Posted: Mon Aug 01, 2011 1:14 pm Post subject: |
|
|
Acolyte
Joined: 18 Jul 2011
Posts: 55
|
| It is not, use the FORMAT in the select statement that you pass to Oracle DB and you get it back again. |
|
| Back to top |
|
|
| Sai K |
| Posted: Mon Aug 01, 2011 1:22 pm Post subject: |
|
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
sorry.. I didn't get you..
I am getting the timestamp: 2011-06-16T11:25:00 from the webservice. I need to take this value and update the database. Database column has datatype as date. |
|
| Back to top |
|
|
| WGerstma |
| Posted: Mon Aug 01, 2011 1:51 pm Post subject: |
|
|
Acolyte
Joined: 18 Jul 2011
Posts: 55
|
| Just insert it, time information is there. Just to get it back/confirm its there use the FORMAT |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Mon Aug 01, 2011 2:07 pm Post subject: Re: Inserting the timestamp value in oracle database from WM |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| Sai K wrote: |
| When I am passing the same value(2011-06-16T11:25:00), it was inserted as 2011-06-16. |
Was it really inserted as 2011-06-16?
How exactly did the broker insert the value into the database?
What component(s) retrieved the data, and how exactly did they do that? |
|
| Back to top |
|
|
| Sai K |
| Posted: Mon Aug 01, 2011 2:22 pm Post subject: |
|
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
You are right WGerstma.
I select the same column and stored into environment variables. Now it is showing the timestamp(2011-07-29 18:08:00.000) value. But why it is not showing as it is in the database when we inserted ?? |
|
| Back to top |
|
|
| Sai K |
| Posted: Mon Aug 01, 2011 7:43 pm Post subject: |
|
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
rekarm01 ---
It was inserted as 01-AUG-11. But we select this value to the environment variables, value is showing like this :: 2011-08-01 22:35:05.000. |
|
| Back to top |
|
|
| WGerstma |
| Posted: Mon Aug 01, 2011 11:25 pm Post subject: |
|
|
Acolyte
Joined: 18 Jul 2011
Posts: 55
|
| Your statement is wrong. It was not inserted as 01-AUG-11. Just the tools you use to check this are the problem, not using the FORMAT to show the real world. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Tue Aug 02, 2011 2:30 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| Sai K wrote: |
| It was inserted |
That's not very descriptive. What inserted the timestamp? And how exactly did it accomplish this?
| Sai K wrote: |
| But we select this value to the environment variables |
We? What performed the actual select? What did the actual select look like? Which environment variables? What were their values?
| Sai K wrote: |
| value is showing like this :: 2011-08-01 22:35:05.000 |
Is that different from the expected outcome? If so, how? |
|
| Back to top |
|
|
|
|
