Question & Answer
Question
How do you emulate the Oracle timestamp with format as .FF4. adding milliseconds?
Answer
Currently, NPS does not have a format type that can be passed to the NPS function to_timestamp that will emulate the Oracle format type .FF4., inclusion of milliseconds.
There is, however, a SQL rewrite that emulates the Oracle format type .FF4. To add in the fractional part of a second, you can add this to the expression:
- ((col_time*10000) % 10000) * interval ‘1 second’ / 10000
The expression ((col_time*10000) % 10000) converts the four digits after the decimal place into an integer representing a number of “ten-thousandths of a second”. Then, the expression multiplies that by an interval of “a ten-thousandth of a second”, which it does by multiplying by an interval of 1 second and dividing by 10000.
For example, the following SQL:
- select to_timestamp (to_char(col_date,'YYYYMMDD') ||ltrim(to_char(floor(col_time),'000000')),'YYYYMMDDHH24MISS') + ((col_time*10000) % 10000) * interval '1 second' / 10000 from test_table ;
Produces the following result:
- ?COLUMN?
------------------------
2008-12-02 09:39:11.49
(1 row)
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ284848
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21574657