Reading a datetime with miliseconds into an Oracle DATE
26 Aug 2020
While fetching information from Instructure’s Bridge learning management system API, I noticed that their course completion timestamps include miliseconds (e.g.
2020-06-30T08:19:16.413-07:00). I needed to load this value into a
DATE-typed column in an Oracle database table.
the_completion_date_string representing my incoming datetime from Bridge, at first I tried to use
TO_DATE( the_completion_date_string , 'yyyy-mm-dd"T"hh24:mi:ss.FF3TZH:TZM' )
Unfortunately, that produced an
ORA-01821 error of
date format not recognized.
Justin Cave pointed out on StackOverflow that since Oracle’s
DATE data type doesn’t store miliseconds, its
TO_DATE() function doesn’t recognize format strings that mention miliseconds.
I checked to see what would happen if I just cut off
.FF3 (which represented the period and the miliseconds) and
TZH:TZM (which represented the negative or positive timestamp indicating offset from UTC time) from my format string:
TO_DATE( the_completion_date_string , 'yyyy-mm-dd"T"hh24:mi:ss' )
No dice. That procduced an
ORA-01830 error of
date format picture ends before converting entire input string.
Combining Justin’s advice with Peter Nosko’s tip on a different StackOverflow question, it turns out that the correct fragment of code is:
CAST( TO_TIMESTAMP_TZ( the_completion_date_string , 'yyyy-mm-dd"T"hh24:mi:ss.FF3TZH:TZM' ) AS DATE )
Later on, I needed to cross-load the date into a
DATE-typed column where none of the values had timestamps.
I was able to further convert the DATE by surrounding it with
My entire solution for parsing the JSON from a Bridge HTTP REST API response into an Oracle
VARCHAR2 as implied by my use of
the_completion_date_string involved other function calls:
- Functions in the PL/SQL
UTL_FILEpackage (official documentation)
JSON_OBJECT_T.PARSE(), available since 12.2 per Carsten Czarski (official documentation)
.get_String()function built into
JSON_OBJECT_T-typed data (official documentation & examples)
I’ll have to save all that for another post.<! -- I could get really fun here by including a 2nd link to prev/next if it's not in your language but it has an alt that is. --> <! -- Also, I need to CSS-style these to float left and right, like paginators. -->