loading...

Reading a datetime with miliseconds into an Oracle DATE

katiekodes profile image Katie Originally published at katiekodes.com on ・2 min read

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:

I’ll have to save all that for another post.

Posted on by:

katiekodes profile

Katie

@katiekodes

Je cherche à vous aider à atteindre vos objectifs #code en #français . My goal is to help you work faster by sharing what I know about #SQL, #Python, and #Salesforce in #English and #French

Discussion

pic
Editor guide