How we can convert sub-string of a clob field to datetime in SQLITE?

Question

Example data fields are like :

DATE+05/06/2022 23:59:59;
DATE+06/05/2022 23:59:59;
DATE+14/05/2022 23:59:59;
DATE+26/05/2022 23:59:59;

SUBSTR data we have in dates as :

05/06/2022 23:59:59
06/05/2022 23:59:59
14/05/2022 23:59:59
26/05/2022 23:59:59

Required date fields are like :

2022-06-05 23:59:59
2022-05-06 23:59:59
2022-05-14 23:59:59
2022-05-26 23:59:59

Things I've tried which are populating NULL as end result:

CASTING to datetime [ CAST('05/06/2022 23:59:59' as datetime)]
Use of datetime() [datetime('05/06/2022 23:59:59')]
Using strftime() [strftime('%Y-%m-%d %H:%M:%S','05/06/2022 23:59:59')]

Links I've found which are not being relevant :

I'm looking for any other way than splitting and concatenating date field.

SQLite is not that flexible with dates. You will have to recreate the dates with sunstring() and concatenation. — forpas, Apr 09 '20 at 09:06
@forpas Is it possible to convert string DD-MM-YYYY HH:MM:SS to YYYY-MM-DD HH:MM:SS by any function(s)? — Vaibhav, Apr 09 '20 at 14:13

score 1 · Accepted Answer · answered Apr 09 '20 at 21:49

SQLite does not have built-in functions to convert strings to dates. You would need to use string functions to recompose the dates.

For a format lke: 'DATE+05/06/2022 23:59:59;', you can do:

substr(mycol, 12, 4) 
|| '-' || substr(mycol, 9,  2)
|| '-' || substr(mycol, 6,  2)
|| ' ' || substr(mycol, 17, 8)

Demo on DB Fiddle:

with mytable as (
    select 'DATE+05/06/2022 23:59:59;' mycol
    union all select 'DATE+06/05/2022 23:59:59;'
    union all select 'DATE+14/05/2022 23:59:59;'
    union all select 'DATE+26/05/2022 23:59:59;'
)
select substr(mycol, 12, 4) 
        || '-' || substr(mycol, 9,  2)
        || '-' || substr(mycol, 6,  2)
        || ' ' || substr(mycol, 17, 8) mydate
from mytable

| mydate              |
| :------------------ |
| 2022-06-05 23:59:59 |
| 2022-05-06 23:59:59 |
| 2022-05-14 23:59:59 |
| 2022-05-26 23:59:59 |

How we can convert sub-string of a clob field to datetime in SQLITE?

1 Answers1