SQLite – TimeStamp to Date

I am trying to convert a timestamp to date in SQLite.

But it give me always Null back, I try many solution I find out, but any solution works for me

Here is my request :

Thats my SQL script, if you want to try:

CREATE TABLE Appointments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fromDateTime TIMESTAMP NOT NULL,
    toDateTime TIMESTAMP NOT NULL,
    name VARCHAR(30) NOT NULL,
    description VARCHAR(200),
    type VARCHAR(50)
);

Insert INTO Appointments ( fromDateTime, toDateTime, name, description ) VALUES
('21/03/2020 15:00:00', '21/03/2020 16:00:00', 'Test', 'Test Description'), 
('22/03/2020 15:00:00', '22/03/2020 16:00:00', 'Test 2', 'Test 2 Description'),    
('22/03/2020 16:00:00', '22/03/2020 17:00:00', 'Test 2', 'Test 2 Description'),    
('22/03/2020 17:00:00', '22/03/2020 18:00:00', 'Test 2', 'Test 2 Description'),           
('21/03/2020 00:00:00', '25/03/2020 23:59:59', 'Test', 'Test Description'),      
('27/03/2020 08:00:00', '21/03/2020 12:00:00', 'Test', 'Test Description'),
('02/03/2020 08:00:00', '10/03/2020 12:00:00', 'Joelle', 'Test Joelle');

Answer

To expand on @forpas comment, SQLite does not have a TIMESTAMP data type, so when you insert values into your fromDateTime and toDateTime column they are converted to one of SQLite’s 5 data storage classes: NULL, INTEGER, REAL, TEXT, BLOB. Since there is no error on INSERT, this gives the impression that SQLite has recognised the value as a timestamp, when in fact the value has just been treated as TEXT. Now to use those values in any of SQLite’s Date and Time functions they must either be an ISO-8601 compatible string, the word now, or a number (interpreted as either a Julian day number or a Unix timestamp dependent on the context). So, you need to change your times to YYYY-MM-DD hh:mm:ss format i.e.

Insert INTO Appointments ( fromDateTime, toDateTime, name, description ) VALUES
('2020-03-21 15:00:00', '2020-03-21 16:00:00', 'Test', 'Test Description'), 
('2020-03-22 15:00:00', '2020-03-22 16:00:00', 'Test 2', 'Test 2 Description'),    
('2020-03-22 16:00:00', '2020-03-22 17:00:00', 'Test 2', 'Test 2 Description'),    
('2020-03-22 17:00:00', '2020-03-22 18:00:00', 'Test 2', 'Test 2 Description'),           
('2020-03-21 00:00:00', '2020-03-25 23:59:59', 'Test', 'Test Description'),      
('2020-03-27 08:00:00', '2020-03-21 12:00:00', 'Test', 'Test Description'),
('2020-03-02 08:00:00', '2020-03-10 12:00:00', 'Joelle', 'Test Joelle');

Note that datetime is simply called with the column as a parameter and returns the string in an ISO-8601 format. To get YYYY-MM-DD format you need to use strftime as well. So your query becomes:

SELECT strftime('%d - %m - %Y', fromDateTime) AS y,
       strftime('%Y-%m-%d', fromDateTime) AS x
FROM Appointments

And the output:

y               x
21 - 03 - 2020  2020-03-21
22 - 03 - 2020  2020-03-22
22 - 03 - 2020  2020-03-22
22 - 03 - 2020  2020-03-22
21 - 03 - 2020  2020-03-21
27 - 03 - 2020  2020-03-27
02 - 03 - 2020  2020-03-02

Demo on dbfiddle