CODEWITHSUNDEEP
oracle-database
Sanjeev Behra
Sanjeev Behra

Reputation: 19

Storing timestamp values stored in a varchar2 column into a date column in oracle

I have a column in a table that stores timestamp values as "2018-01-12 16:13:51.107000000", i need to insert this column into a date column in another table, what format mask do i have to use here..

I have used the mask 'YYYY-MM-DD HH24:MI:SS.FF' but shows 'date format not recognized'.

Upvotes: 0

Views: 583

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

I am assuming that you were trying to use TO_DATE on your text timestamp data. This won't work, because Oracle dates do not store anything more precise than seconds. Since your timestamps have fractional seconds, you may use TO_TIMESTAMP here, then cast that actual timestamp to a date:

SELECT
    CAST(TO_TIMESTAMP('2018-01-12 16:13:51.100000',
                      'YYYY-MM-DD HH24:MI:SS.FF') AS DATE)
FROM dual;

12.01.2018 16:13:51

Demo

Upvotes: 1

user5683823
user5683823

Reputation:

You can do this with a single call to TO_DATE(), but you must give the correct format model. Note that this solution is simpler (and possibly faster - if that matters) than converting to a timestamp and then casting to date.

If you want TO_DATE() to ignore part of the input string, you can use the "boilerplate text" syntax in the format model. That is enclosed in double quotes. For example, if your string included the letter T somewhere and it had to be ignored, you would include "T" in the same position in the format model.

This has some flexibility. In your case, you must ignore the decimal point, and up to nine decimal digits (the maximum for timestamp in Oracle). The format model will allow you to use ".999999999" (or any other digits, but 9999... is used by most programmers) to ignore a decimal point and UP TO nine digits after that.

Demo: (notice the double-quoted "boilerplate text" in the format model)

select to_date('2018-01-12 16:13:51.100000', 
               'YYYY-MM-DD HH24:MI:SS".999999999"') as dt
from   dual;

DT                 
-------------------
2018-01-12 16:13:51

Upvotes: 0

Related Questions