SQL converter varchar to Timestamp 'YYYY-MM-DDTHH:MM:SS#Z' format to 'HH:MM:SS'-CodePudding

I have a dataset/table in SQLite with this format '2022-02-21T17:36:48.311Z' varchar and I would like to convert it to Timestamp creating two others new columns one for date/day values'YYYY-MM-DD' and another with the "hours" values HH:MM:SS.

I tried:

select TO_CHAR(TO_DATE(column_name, 'YYYY-MM-DDTHH:MM:SS'),  'YYYY-MM-DD T HH:MM:SS')
from table

but it does not work.

The column_name is a varchar dataformat, and as example of value from this column are

2022-02-21T07:22:46.777Z
2022-02-21T07:22:57.877Z
2022-02-21T07:23:27.362Z
2022-02-21T07:23:28.212Z

Any suggestion ? The idea is to "break" this column_name into two another columns, one with days and the other with hours values.

CodePudding user response：

Try the following:

select (substr(d, 1, 4) || '-' || substr(d, 6, 2) || '-' || substr(d, 9, 2)) as DateVal,
(substr(d, 12, 2) || ':' || substr(d, 15, 2) || ':' || substr(d, 18, 2)) as TimeVal
from Dates;

select Date(d) as DateVal, Time(d) as TimeVal from Dates;

See a demo using SQLite v3.30 from db-fiddle.