CODEWITHSUNDEEP
oracle-databasedatetime
rajesh
rajesh

Reputation: 1

I have oracle database data in diffrent time format stored need to change to another time format

I have date formate like 20111125101924Z. I need to remove Z and need to made this format like DD/MM/YYYY/hr/mn/sec

Upvotes: 0

Views: 33

Answers (2)

MT0
MT0

Reputation: 167867

A DATE and TIMESTAMP data type are binary formats (consisting of 1 byte for each of century, year-of-century, month, day, hour, minute and second and for timestamp may also have up to 6 bytes for fractional seconds and/or 7 bytes for time zone) and they do NOT have a format.

Therefore, if you are trying to copy from DATE to DATE or TIMESTAMP to TIMESTAMP then you do not need to do anything; any differences in how they are displayed is purely cosmetic and can be modified by changing the session variables or settings on whatever client user interface you are using to view the database.

For example, in SQL/Plus (and SQL Developer), you can use the session parameter NLS_DATE_FORMAT to change how a date is displayed and your two formats would be:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYYHH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS"Z"';

If you are storing the values as strings then

  • don't do that, change the data type to DATE or TIMESTAMP.

  • if you must then change it using TO_DATE and TO_CHAR:

    TO_CHAR(
  TO_DATE(
    '20111125101924Z',
    'YYYYMMDDHH24MISS"Z"'
  ),
  'DD/MM/YYYYHH24MISS'
)

  • Or, for TIMESTAMP WITH TIME ZONE:

    TO_CHAR(
  TO_TIMESTAMP_TZ(
    '20111125101924Z',
    'YYYYMMDDHH24MISSTZR'
  ),
  'DD/MM/YYYYHH24MISS'
)

Upvotes: 2

Mahamoutou
Mahamoutou

Reputation: 1555

You could try this, I assume your column is of string data type

select 
  to_char( 
    to_date(REPLACE('20111125101924Z', 'Z'), 'YYYYMMDDHHMISS')
    , 'DD/MM/YYYY/HH/MI/SS') 
from dual
;

Upvotes: 0

Related Questions