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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With