Convert '7-apr-11' To Dd-mm-yyyy In Sqlite

I'm trying to convert a date that has the month stored in 3 characters to a workable date format such as DD-MM-YYYY. I have looked at the strftime() function and I can't find anyt

Solution 1:

Doesn’t look as if SQLite was able to handle that format as a date natively. If you can only use SQLite (and can’t do a transformation of the data outside of it, e.g. in a scripting language processing the data), then you could go with nested replace calls, something like

replace(
  replace(
    replace(
      original_value,
      'Jan',
      '01'
    ),
    'Feb',
    '02'
  ),
  'Mar',
  '03'
)

etc., and ultimately make the year value a four-digit one using substring functionality.

Will probably not have the best performance, but for a one-time(?) data conversion it might be OK.

Solution 2:

For complete usage of date and time formatting, look at the documentation.

Solution 3:

You will need to manually parse date.

In a single query:

SELECTCASEWHEN d<9THEN'0'||d ELSE d END||'-'||mmt.mm||'-'||CASEWHEN yy<80THEN2000+yy ELSE1900+yy ENDFROM (
    SELECTCAST(SUBSTR(dt,1,s1-1) ASINTEGER) AS d, SUBSTR(dt,s1+1,s2-s1-1) AS mmm, CAST(SUBSTR(dt, s2+1) ASINTEGER) AS yy FROM (
        SELECT dt, s1, INSTR(SUBSTR(dt,s1+1),'-') + s1 AS s2 FROM (
            SELECT dt, INSTR(dt, '-') AS s1 FROM (
                SELECT'7-Apr-11'AS dt -- <<< Put your date here!
            )
        )
    )
) AS src LEFTJOIN (
    SELECT'01'AS mm, 'Jan'AS mmm
    UNIONSELECT'02', 'Feb'UNIONSELECT'03', 'Mar'UNIONSELECT'04', 'Apr'UNIONSELECT'05', 'May'UNIONSELECT'06', 'Jun'UNIONSELECT'07', 'Jul'UNIONSELECT'08', 'Aug'UNIONSELECT'09', 'Sep'UNIONSELECT'10', 'Oct'UNIONSELECT'11', 'Nov'UNIONSELECT'12', 'Dec'
) AS mmt ON src.mmm=mmt.mmm;

Y2K: This will assume year<80 as 20xx, else 19xx.