Change timestamp format in oracle

0
votes

I am getting timestamp format as '01-APR-21 12.02.00.496677000 AM' from oracle, I want to change the format to load data into sql server column with datatype as datetime.

Current Input: '01-APR-21 12.02.00.496677000 AM' Expected Output : 2021-04-01 12:02:00.496

I need to write the code in oracle to change timestamp format

Thanks Neha

1
oracle
The default format is defined by your session's nls_timestamp_format. You could change that for the current session but it seems very weird that you care about the display format of a timestamp if you're moving it to a different database. Any ETL tool should just move the data from a timestamp column to a datetime without worrying about what format the current session would use if it wanted to do an implicit conversion of the timestamp to a varchar2. - Justin Cave
i cannot change anything in the oracle database as it it not owned by us. I am not using any ETL tool i have to load data into different database via mule4 - Neha Singh
You can change your session settings, that doesn't change anything in the database. If you are writing your own ETL process for extracting the data then you need to explain how you are doing it. If, for example, you're querying a table and writing the output to a text file that you later use to load into SQL Server, then you can set the session's format, or use to_char() with the format you require for each date/timestamp column. But we need to know what you are doing to be able to help you. - Alex Poole
It seems unlikely that MuleSoft is unable to take a timestamp from an Oracle database and move it to a datetime in SQL Server without requiring an implicit conversion of the timestamp to a varchar2. You could, of course, do an explicit to_char on the timestamp to convert it to a string in whatever format you want. But, again, it seems unlikely that this step would be necessary. - Justin Cave
The point is that format is an output property, not inherent in the data itself. You can alter your session (this does not change the database) to change the default output format, or you can use to_char to change the output format in a query, but these only affect how data is displayed as characters. Migrating data from timestamp in Oracle to datetime in SQL Server shouldn't care about character format output; the ETL tool should be able to perform a direct conversion from one datatype to the other. - pmdba

1 Answers

2
votes

A TIMESTAMP is a binary data format consisting of 7-20 bytes (century, year-of-century, month, day, hour, minute, second, up to 6 bytes for fractional seconds and up to 7 bytes for time zone information); it does NOT have a format.

Why am I seeing the TIMESTAMP with a format?

You are seeing it with a format because whatever user interface you are using to access the database has decided that it is more useful to display the binary information as a formatted string rather than returning the raw byte values to you.

Typically, for SQL/Plus and SQL Developer, this is managed by the NLS_TIMESTAMP_FORMAT session parameter. Other user interfaces will have different mechanisms by which they manage the default format of dates and timestamps.

If you want to change the default for SQL/Plus (and SQl Developer) then you can use:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';

(Or whatever format you would like.)

How can I format the TIMESTAMP value?

If you then want to display the timestamp with a format (remember, a TIMESTAMP is not stored with any format) then you want to use TO_CHAR to convert it to a string where it can have a format.

If you want to format the TIMESTAMP as a YYYY-MM-DD HH24:MI:SS.FF3 string then use:

SELECT TO_CHAR( your_timestamp_column, 'YYYY-MM-DD HH24:MI:SS.FF3' )
FROM   your_table

How can I convert a formatted string back to a TIMESTAMP?

From your comment:

I loaded the oracle data into a different SQL table where date format was varchar and data got loaded as '01-APR-21 12.02.00.496677000 AM'

Use TO_TIMESTAMP:

SELECT TO_TIMESTAMP( your_string_column, 'DD-MON-RR HH12:MI:SS.FF9 AM' )
FROM   your_table

(Note: that this will convert your string to a binary TIMESTAMP and then whatever user interface you are using will use its rules on how to display it; and if the default format it is using is DD-MON-RR HH12:MI:SS.FF9 AM then the query above will look like it has done nothing; it has, the UI is just implicitly converting it back to a string to display it.)