How to convert text to date (YYYY-MM-DD format) in SQLite-CodePudding

So i have a table named 'employe' with the column 'employee_hire_date' (type text, DD-MM-YYYY format) and i want to format all the data to date type, or at least change the format with an update to make it YYYY-MM-DD, how can I do this?

CodePudding user response：

Since it's a fixed-width format, you can use substr to get all the pieces and then reassemble them by concatenating them with ||.

-- DD-MM-YYYY -> YYYY-MM-DD
update employees
set employee_hire_date =
  -- YYYY
  substr(employee_hire_date, 7, 4) || '-' ||
  -- MM
  substr(employee_hire_date, 4, 2) || '-' ||
  -- DD
  substr(employee_hire_date, 1, 2);

The datatype of the column does not matter.

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.