Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Girish.

Asked: March 11, 2019 - 6:40 am UTC

Last updated: March 11, 2019 - 10:52 am UTC

Version: 18c

Viewed 1000+ times

You Asked

Hi,

I have data in the format 2019-02-21T13:58:18.000+00:00 in a varchar2 column(loaded into staging table from a CSV file). When merging this staging table I need to put this value into a DATE data type column. How to do this ? Format of the date need to be same.

Currently when I do without any conversion I am getting below error

declare
*
ERROR at line 1:
ORA-01861: literal does not match format string
ORA-06512: at line 24


Any help or guidance is appreciated

Thanks,
Girish

and Chris said...

Your date value has fractional seconds and timezone details. Oracle Database DATEs only go down to the second. And don't support timezones.

So either:

You need to trim this information off the end
with val as (
  select '2019-02-21T13:58:18.000+00:00' v
  from   dual
)
  select to_date ( 
           substr ( v, 1, 19 ) , 
           'FMYYYY-MM-DD"T"HH24:MI:SS' 
         ) dt
  from   val;

DT                     
21-FEB-2019 13:58:18


OR

You can use the # format to ignore the characters at the end:

with val as (
  select '2019-02-21T13:58:18.000+00:00' v
  from   dual
)
  select to_date ( v, 'FMYYYY-MM-DD"T"HH24:MI:SS.###+##:##' ) dt
  from   val;

DT                     
21-FEB-2019 13:58:18


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.