CODEWITHSUNDEEP
sqloracle-database
Leandro Silva
Leandro Silva

Reputation: 41

convert oracle date format to insert

I am recovering the insert from a log file after the application was unable to insert it.

For security reasons, unfortunately I can't put the insert here.

I need to insert some data but I'm having problems with my insert with the date. How should i convert this date 'Tue Dec 31 12:28:59 BRT 2019' to be able to insert in a date column.

[]´s

Upvotes: 4

Views: 215

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

A variation on @OldProgrammer's replace approach is to embed the 'BRT' as a character literal in the format mask:

select to_date('Tue Dec 31 12:28:59 BRT 2019',
  'DY MON DD HH24:MI:SS "BRT" YYYY',
  'NLS_DATE_LANGUAGE=ENGLISH') as result
from dual;

RESULT
-------------------
2019-12-31 12:28:59

I've included the optional third argument to to_date() so that day and month names are always interpreted in English; otherwise the session settings would be used, which could cause it to fail.

I know you aren't interested in the time zone, but if you did want to take that into account, you're still a bit stuck as the abbreviation BRT can't be translated to a single region or offset.

With a limited range of abbreviations that are unambiguous to you, you could go back to replace() to substitute the region name instead, e.g.:

select to_timestamp_tz(replace('Tue Dec 31 12:28:59 BRT 2019', 'BRT', 'America/Sao_Paulo'),
    'DY MON DD HH24:MI:SS TZR YYYY',
    'NLS_DATE_LANGUAGE=ENGLISH') as ts_result,
  cast(to_timestamp_tz(replace('Tue Dec 31 12:28:59 BRT 2019', 'BRT', 'America/Sao_Paulo'),
    'DY MON DD HH24:MI:SS TZR YYYY',
    'NLS_DATE_LANGUAGE=ENGLISH') as date) as date_result,
  to_timestamp_tz(replace('Tue Dec 31 12:28:59 BRT 2019', 'BRT', 'America/Sao_Paulo'),
    'DY MON DD HH24:MI:SS TZR YYYY',
    'NLS_DATE_LANGUAGE=ENGLISH') at time zone 'UTC' as utc_ts_result,
  cast(to_timestamp_tz(replace('Tue Dec 31 12:28:59 BRT 2019', 'BRT', 'America/Sao_Paulo'),
    'DY MON DD HH24:MI:SS TZR YYYY',
    'NLS_DATE_LANGUAGE=ENGLISH') at time zone 'UTC' as date) as utc_date_result
from dual;

TS_RESULT                               DATE_RESULT         UTC_TS_RESULT             UTC_DATE_RESULT
--------------------------------------- ------------------- ------------------------- -------------------
2019-12-31 12:28:59.0 AMERICA/SAO_PAULO 2019-12-31 12:28:59 2019-12-31 14:28:59.0 UTC 2019-12-31 14:28:59

This is slightly simplified by not having to worry about DST here. But as you can see you can then convert easily to another time zone if needed.

Upvotes: 7

OldProgrammer
OldProgrammer

Reputation: 12169

This seems to work:

select to_date(replace('Tue Dec 31 12:28:59 BRT 2019','BRT',''),'DY MON DD HH24:MI:SS  YYYY') from dual

Upvotes: 6

Related Questions