Home » SQL & PL/SQL » SQL & PL/SQL » Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged)
Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged) [message #681052] Tue, 09 June 2020 23:34 Go to next message
bmccollum
Messages: 8
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!
Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure [message #681053 is a reply to message #681052] Tue, 09 June 2020 23:35 Go to previous messageGo to next message
bmccollum
Messages: 8
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!
Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure [message #681054 is a reply to message #681052] Tue, 09 June 2020 23:36 Go to previous messageGo to next message
bmccollum
Messages: 8
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 #681055 is a reply to message #681052] Wed, 10 June 2020 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Same answer than in your first topics:

Michel Cadot wrote on Thu, 02 April 2020 07:11

Something is wrong in your code so post it.
Before, Please read How to use [code] tags and make your code easier to read.
Indent the code, make sure that lines of code do not exceed 100 characters.
If you don't know how to format the code, learn it using SQL Formatter.

And we are still waiting for your feedback in your other ones.


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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
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.
Re: Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged) [message #681067 is a reply to message #681061] Wed, 10 June 2020 09:52 Go to previous messageGo to next message
bmccollum
Messages: 8
Registered: April 2020
Junior Member
So sorry for the exact same msg. being posted numerous times here. Just noticed this right now. The topic didn't appear to be created successfully several times in a row late last night when I was attempting to post this, so I just tried a few times until I finally got confirmation that the topic was indeed posted. I see it's on here several times in a row now. My bad. Will try to remove the duplicates if I can see how to do that in this forum. I'm reading through a few of your comments/suggestions in just a min. and will post back results of anything that's been recommended for me to try. Thank you again.
Re: Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged) [message #681069 is a reply to message #681067] Wed, 10 June 2020 13:25 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
in the past I have had some SQL server applications that were just fighting me on passing timestamps. What I ended up doing was to send a timestamp as a character string and then use to_timestamp to convert it internally to my timestamp. I haven't had to do that too often, but when I did it always works
Re: Time Portion of Date/Time Value Being Stripped When Passed to Stored Procedure (3 merged) [message #681074 is a reply to message #681067] Thu, 11 June 2020 03:06 Go to previous message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
I see that you have posted your topic again, this time as a blog article. I have not approved it yet, because as written it isn't a blog but a question. If you could edit the article to include the solution, thus being useful to others, it will be approved no problem.
Previous Topic: Multiple rows into on "TEMP" table?
Next Topic: The ESCAPE Option
Goto Forum:
  


Current Time: Wed Sep 23 02:49:19 CDT 2020