Reading a datetime with miliseconds into an Oracle DATE

26 Aug 2020 🔖 databases sql integration api json instructure
💬 EN

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
)

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 TRUNC().

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_FILE package (official documentation)
• JSON_OBJECT_T.PARSE(), available since 12.2 per Carsten Czarski (official documentation)
• The .get_String() function built into JSON_OBJECT_T-typed data (official documentation & examples)

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