How to get the day of the week from a date in mm/dd/yyyy format?

Question

I am using DB Broswer for SQLite. I would like to retrieve information about the month and the day of the week for each sample.

SELECT Date
FROM Xtrain

returns

Date
26/04/2018
2/4/2018

I have tried using the TO_DATE statement.

Just to see if the function works I tried:

SELECT TO_DATE('2022-01-01','YYYY-MM-DD');

I get the following error:

Execution finished with errors.
Result: no such function: TO_DATE
At line 1:
SELECT TO_DATE('2022-01-01','YYYY-MM-DD');

Answer 1

Issue

There is no built in TO_DATE function in SQLite.

Fixing The Issue

The available date time functions can be found here https://www.sqlite.org/lang_datefunc.html. However, the functions expect/require the dates to be in a specific set of formats (which your data does not adhere to), see the link regrading formats.

As such you would need to reformat your date to either use the functions or to get the date in the format you require. Due to the format in which they are saved the reformatting is a little complex as you have to cater for single and double numeric characters in both the day of the month and the month of the year.

As an example using the CASE WHEN THEN END construct, the substr function and the concatenate operator (||)you could use a query such as:-

SELECT
CASE 
    WHEN substr(date,2,1) = '/' AND substr(date,4,1) = '/' THEN '0'||substr(date,3,1)||'/0'||substr(date,1,1)||'/'||substr(date,5,4)
    WHEN substr(date,2,1) = '/' AND substr(date,5,1) = '/' THEN substr(date,3,2)||'/0'||substr(date,1,1)||'/'||substr(date,6,4)
    WHEN substr(date,3,1) = '/' AND substr(date,5,1) = '/' THEN '0'||substr(date,4,1)||'/'||substr(date,1,2)||'/'||substr(date,6,4)
    ELSE substr(date,4,3)||substr(date,1,2)||substr(date,6,5)
    END
    AS date 
FROM xtrain;

Demo

The following demonstrates use of the above:-

DROP TABLE IF EXISTS xtrain;
CREATE TABLE IF NOT EXISTS xtrain (date TEXT);
INSERT INTO xtrain VALUES ('26/4/2018'),('2/2/2018'),('3/04/2018'),('10/04/2018');
SELECT date AS asis_date,
CASE
    /* handle d/m/yyyy */
    WHEN substr(date,2,1) = '/' AND substr(date,4,1) = '/' THEN '0'||substr(date,3,1)||'/0'||substr(date,1,1)||'/'||substr(date,5,4)
    /* handle d/mm/yyyy */
    WHEN substr(date,2,1) = '/' AND substr(date,5,1) = '/' THEN substr(date,3,2)||'/0'||substr(date,1,1)||'/'||substr(date,6,4)
    /* handle dd/m/yyyy */
    WHEN substr(date,3,1) = '/' AND substr(date,5,1) = '/' THEN '0'||substr(date,4,1)||'/'||substr(date,1,2)||'/'||substr(date,6,4)
    /* othewise must(should) be dd/mm/yyyy */
    ELSE substr(date,4,3)||substr(date,1,2)||substr(date,6,5)
    END
    AS formatted_date 
FROM xtrain;
DROP TABLE IF EXISTS xtrain;

The result being:-

• There are probably many ways, the above (imo) clearly shows all of the steps in detail (so may well be less concise than alternatives).

• Really the correct solution would be to store the date in a consistent format and preferably one of the recognised formats. Doing so would afford full and direct data handling capabilities via the date and time functions.