SQLite - DATES

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").
• 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.

To be compatible with other SQL databases, SQLite allows to use the common datatype names which is used in other databases and maps them to their basic SQLite datatypes.

The below table shows how the common date datatypes in other databases are interpreted in SQLite. See also: SQLite Data Types.

Example: Create a table with date datatype

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Date_of_Joining.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Date_of_Joining DATE
);

This will create a empty table named Employee containing five columns.

Example: Working with date datatype

Consider a database table called Employee with the following records:

To fetch data of all employees present in the Employee table who joined the organization on '2019-05-25', the query is:

SELECT * FROM Employee
WHERE Date_of_Joining = '2019-05-25';

This will produce the result as shown below:

Complete Date and Time Reference

For a complete reference of all SQLite Date and Time functions, see the SQLite Data and Time Reference.