If you’re getting an error that reads something like “unknown datatype for (columnname): “DATE”” in SQLite, it appears that you’re trying to define a column as a DATE type in a strict table.

SQLite doesn’t support the DATE type, however, this error should only occur on strict tables (i.e. a table defined as STRICT).

To fix this issue, either use a supported data type or make the table a regular (non-strict) table.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    hire_date DATE NOT NULL,
    salary REAL NOT NULL
) STRICT;

Output:

Parse error: unknown datatype for employees.hire_date: "DATE"

In this case I tried to make the hire_date column a DATE type. As expected, an error is returned indicating that DATE is an unknown type.

Solution 1

One way to fix this issue is to use a supported data type. When using strict tables, we can only use the following data types (at least, at the time of writing):

• INT
• INTEGER
• REAL
• TEXT
• BLOB
• ANY

So we can define our column as say, TEXT:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    hire_date TEXT NOT NULL,
    salary REAL NOT NULL
) STRICT;

Solution 2

If you really must define the column as DATE, then you’ll need to remove the STRICT keyword:

So we can define our column as say, TEXT:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    hire_date DATE NOT NULL,
    salary REAL NOT NULL
);

This obviously makes the table a non-strict table, so you’ll need to be comfortable with that.

Alternative Types for Representing Date Values

The SQLite documentation mentions that the date and time functions are capable of storing dates and times as either TEXT, REAL, or INTEGER for date columns. In particular,

• TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

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.