Function to convert char(255) non standard dates into timestamps

Question

I have database with a column of date strings that are in the format YYYY-MM-DDThh:mm:ss.3nZ I would like to transform them into TIMESTAMP so that I can then apply the BETWEEN function to identify rows that fall in a given time window (this being the ultimate goal of the exercise). I can't just change the nature of the column unfortunately. What is the best way to achieve this? Thank you

Answer 1

Try this:

SELECT
    to_timestamp('2021-01-28T10:26:32.359Z', 'YYYY-MM-DD"T"HH:MI:SS.FF3"Z"') t
FROM
    dual;