Oracle SQL: select for weekday and converting column to date

I’m having a database with column (with datatype VARCHAR20)

Created
2020-01-01T20:30:20.207Z
2020-01-04T23:10:00.242Z

In the end I want to be able to filter for a certain weekday. My first step in order to do that is applying

SELECT *
FROM mydatabase
WHERE TO_DATE(CREATED, 'YYYY-MM-DD"T"HH24:MI:SS.FFF"Z"') BETWEEN TO_DATE ('2020-01-02T10:39:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FFF"Z"') AND TO_DATE('2020-01-03T15:39:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FFF"Z"');

to see if conversion corresponding to my ISO8601 format works. Unfortunately, I get the error “ORA-01821 – “date format not recognized””. Would be great if someone could tell me how to accomplish my task of filtering according to a certain weekday and if I’m on the right track.

All the best.

Answer

I would just do:

where to_char(to_date(substr(created, 1, 10), 'YYYY-MM-DD'), 'Dy') = 'Mon'

For internationalization purposes, you can insist this is in English using a third parameter:

select to_char(to_date(substr(created, 1, 10), 'YYYY-MM-DD'), 'Dy') = 'Mon'

Or use the native language of the database.

This converts the first ten characters to a date and then converts that to a weekday.