Just for SQLite, is there an easy way to convert a column of text (like 21-Sep-2022) into a valid date format while query?

useraFEu 0
Viewed 59

Just for SQLite, is there an easy way to convert a column of text (like 21-Sep-2022) into valid date format while query?

I know it's easy for other DBs, such as SQL Server and Oracle, to do so. They have existing function. I'm now meet the same situation in operating SQLite. But I did not find any "cast", "convert" or "date" function that could work and get a proper result.

I've tried DATE(), and it seems the text is not recognized and only NULL returns.

sqlite
1 Answers

Something like this should do the job. Field name "f", table name "x".

select 
    -- YEAR
    printf('%04d-',substr( f ,-4)) ||
       -- LOOKUP FUNCTION for MONTH
       printf('%02d-',
       CASE substr(f, instr(f,'-')+1,3 )
             WHEN 'Jan' THEN 1
             WHEN 'Feb' THEN 2
             WHEN 'Mar' THEN 3
             WHEN 'Apr' THEN 4
             WHEN 'May' THEN 5
             WHEN 'Jun' THEN 6
             WHEN 'Jul' THEN 7
             WHEN 'Aug' THEN 8
             WHEN 'Sep' THEN 9
             WHEN 'Oct' THEN 10
             WHEN 'Nov' THEN 11
             WHEN 'Dec' THEN 12
          END)
           || 
      -- DAY
        printf('%02d', substr(f, 1, instr(f,'-')) )     
     as thedate
     from x 
     

+-------------+
|   Table f   |
+-------------+
| 1-Jan-2023  |
| 19-Sep-2022 |
| 24-Dec-1989 |
+-------------+

+------------+
|  thedate   |
+------------+
| 2023-01-01 |
| 2022-09-19 |
| 1989-12-24 |
+------------+

The result is formatted YYYY-MM-DD, and can be processed as a date in SQLite.

Function will fail if some dates are not formatted correctly.

MyICQ0
Related