CODEWITHSUNDEEP
sqloracle-database
Arun
Arun

Reputation: 41

how to remove last few extra zeroes from timestamp column in oracle?

i need to remove few extra zeroes from timestamp column

Eg: 04-NOV-17 09.22.47.000000000 PM

i want something like this : 04-NOV-17 09.22.47.000

only first three zeroes from milliseconds

i tried this "TO_CHAR(column_name,'YYYY-MON-DD HH24:MI:SS.FF3')" but it convert it into string but i need only in timestamp format.

Upvotes: 1

Views: 4611

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59513

Solution from @jarlh is right.

For example CAST(TIMESTAMP '2018-06-19 08:54:41.928513' AS TIMESTAMP(3)) returns 2018-06-19 08:54:41.929

However, your default output format for TIMESTAMP seems to be DD-MON-YY HH.MI.SS.FF9 PM thus on output it is always padded with zeros.

Try 

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YY HH.MI.SS.FF PM';

then you should get only relevant digits for FF - three in your case.

From documentation Datetime Format Elements

FF [1..9]

Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in DATE formats.

Upvotes: 2

Related Questions