Salesforce, Python, SQL, & other ways to put your data where you need it -- a bilingual blog in English & French

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

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:

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

--- ---