Reading a datetime with miliseconds into an Oracle DATE

#database #sql #oracle #tips

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.

With the_completion_date_string representing my incoming datetime from Bridge, at first I tried to use TO_DATE():

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
)

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: