Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged) [message #681052] |
Tue, 09 June 2020 23:34 |
|
bmccollum
Messages: 15 Registered: April 2020
|
Junior Member |
|
|
I have a SQL Server Integration Services package that ultimately sends several datetime-related parameters to an Oracle stored procedure for updating the corresponding columns (that are of TimeStamp(3) data type).
For the life of me, I can't get the resulting data that shows up in the Oracle table to actually contain the full portions of the datetime value I've passed into the stored procedure.
I'm basically wanting to end up with (in Oracle):
09-JUN-20 11.19.47.710 AM
But I'm instead always ending up with (in Oracle):
09-JUN-20 02.00.00.000 AM
I can run something like this directly in Oracle and it produces the expected result containing every last part of the datetime value:
insert into tbtest(partnumber, date_end)
values('A100',to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH:MI:SS.FF3'),'YYYY-MM-DD HH:MI:SS.FF3'))
Result: 09-JUN-20 11.19.47.710 AM
Any help is greatly appreciated, as what I'm trying to insert has to maintain the full details of the date/time value down to and including the milliseconds.
Thanks!
|
|
|
|
|
|
Re: Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged) [message #681061 is a reply to message #681055] |
Wed, 10 June 2020 05:43 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
First of all:
insert into tbtest(partnumber, date_end)
values('A100',to_timestamp(to_char(systimestamp,'YYYY-MM-DD HH:MI:SS.FF3'),'YYYY-MM-DD HH:MI:SS.FF3'))
will produce wrong result since HH is 12 hour clock. Therefore the above INSERT will lose 12 hours for any afternoon timestamp:
SQL> select timestamp '2020-06-09 23:15:10' ts,to_timestamp(to_char(timestamp '2020-06-09 23:15:10','YYYY-MM-DD HH:MI:SS.FF3'),'YYYY-MM-DD HH:MI:SS.FF3') wrong_ts from dual;
TS WRONG_TS
------------------------------- -------------------------------
09-JUN-20 11.15.10.000000000 PM 09-JUN-20 11.15.10.000000000 AM
SQL>
Secondly, you completely misunderstand timestamps and dates. Why do you convert timestamp to string and then back to timestamp???
SY.
|
|
|
|
|
|