-2

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
David Smith
  • 893
  • 2
  • 9
  • 19
  • 2
    SQLite only has few formats recognized as a date. Your task has been answered several times on stackoverflow. A [similar answer is here](https://stackoverflow.com/questions/73677113/how-to-convert-a-date-of-the-format-april-9-2013-into-the-format-yyyy-mm-dd/73677918#73677918). Depending on your consistency, the job may be easier - like if you have `01-Jan-2022` instead of `1-Jan-2022`. – MyICQ Sep 21 '22 at 08:15
  • Year: rightstring(4). Day: strf(%02d) until position of "-". Month: 3 positions later than "-", then lookup. Remember 2 digits. All joined by "-". As per example in link. Then it should work. But doing it in an external script like Python would probably be faster. – MyICQ Sep 21 '22 at 08:45
  • [A meta question](https://meta.stackoverflow.com/questions/420462/why-is-block-code-format-text-still-interpreted-in-display) is about formatting of text in an answer. – Peter Mortensen Sep 21 '22 at 22:19

1 Answers1

0

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.

MyICQ
  • 987
  • 1
  • 9
  • 25