OraFAQ Forum: General » date format/timestamp with milliseconds

Home » Other » General » date format/timestamp with milliseconds (Oracle 11g R2 AIX, Oracle 7.3.4 Solaris 8, ODI 10g AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

date format/timestamp with milliseconds [message #652963]

Thu, 23 June 2016 03:04

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Sirs,

After we migrated our legacy system database (from 7.3.4 to 11.2.0.2.0)
"bridging" using the ODI (10.1.3.5), we found out that date formats/timestamp
of transactions extracted had no milliseconds.
Our end-users is requesting our group to extract its original timestamp with
inclusion of milliseconds. Can you assist us on how to do this?

Is there a parameter in ODI that we will setup in order to extract succeeding
transactions with the desired date format/timestamp?

I attached a screen capture of the 2 Oracle databases with their respective
value of NLS_DATE_FORMAT & NLS_TIME_FORMAT.

Thank you in advance!
/forum/fa/13173/0/

[mod-edit: imaged inserted into message body by bb]

Attachment: 11gR2-vs-v734.jpg
(Size: 154.33KB, Downloaded 10073 times)

[Updated on: Fri, 24 June 2016 14:20] by Moderator

Report message to a moderator

Re: date format/timestamp with milliseconds [message #652966 is a reply to message #652963]

Thu, 23 June 2016 03:17

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

SQL> select sysdate, systimestamp from dual;
SYSDATE             SYSTIMESTAMP
------------------- ---------------------------------------------------
23/06/2016 10:15:39 23/06/2016 10:15:39.234 +02:00

SQL> show parameter nls%format
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------------
nls_date_format                      string      DD/MM/YYYY HH24:MI:SS
nls_time_format                      string      HH.MI.SSXFF AM
nls_time_tz_format                   string      HH.MI.SSXFF AM TZR
nls_timestamp_format                 string      DD/MM/YYYY HH24:MI:SS.FF3
nls_timestamp_tz_format              string      DD/MM/YYYY HH24:MI:SS.FF3 TZR

Report message to a moderator

Re: date format/timestamp with milliseconds [message #652967 is a reply to message #652966]

Thu, 23 June 2016 03:25

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

I'm pretty sure timestamp didn't exist in oracle 7. In which case where exactly is this millisecond data stored for you to extract? Dates don't hold milliseconds.

Report message to a moderator

Re: date format/timestamp with milliseconds [message #652973 is a reply to message #652967]

Thu, 23 June 2016 03:47

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think the question is how to get the milliseconds when migrating from Oracle7 to 11gR2.

Report message to a moderator

Re: date format/timestamp with milliseconds [message #652976 is a reply to message #652973]

Thu, 23 June 2016 03:50

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

If he's talking about how to store milliseconds for transactions made on the new system then I don't see how the old system is relevant.
If he wants to pull millisecond data from the old system then that goes back to my question above - where's the data stored in the old system.

Report message to a moderator

Re: date format/timestamp with milliseconds [message #653112 is a reply to message #652976]

Tue, 28 June 2016 00:37

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Sir Michel got my point.

Since as per your confirmation that Oracle 7.3.4 have no timestamp parameter, it's not possible either to add the milliseconds in Oracle 11g.

Thanks to both of you for such clarifications.

Best regards,

Report message to a moderator

Re: date format/timestamp with milliseconds [message #653118 is a reply to message #653112]

Tue, 28 June 2016 03:19

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's not that 7.3.4 doesn't have the parameter. It doesn't have the datatype. Date datatype doesn't store milliseconds in any version.

Report message to a moderator

Re: date format/timestamp with milliseconds [message #653120 is a reply to message #653112]

Tue, 28 June 2016 03:28

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think you have a misunderstanding.
In 11g, you have basically 2 datetime datatypes (ignoring the notion of time zone): the ever existed DATE data type with a granularity up to the second and the TIMESTAMP data type with a granularity up to the nanosecond.
If you want a granularity of millisecond you have to change all your table columns from DATE to TIMESTAMP and your application code to handle this later one.

Report message to a moderator

Previous Topic:	Design Approach
Next Topic:	Ora-03134

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 02:48:52 CDT 2025