SQLite Custom Formatting Date using strftime

Question

I have a small table that contains dates. For a particular report I want to output just the month and day without the year. “Jun. 09” instead of “06/09/2020”. I’ve been experimenting with strftime but I can get it to work. strftime only give me NULL.

Here is the table definition:

CREATE TABLE "visit_log" ("visit_date" DATE, "visit_time" TIME, "client_relation" TEXT, "household_size" INTEGER)

Here is a sample of its content:

rowid   visit_date  visit_time  client_relation household_size
12  06/09/2020      Drop-In 8
13  06/09/2020      Drop-In 5
15  06/16/2020      Scheduled   1
16  06/16/2020      Scheduled   1
17  06/16/2020      Drop-In 4
18  06/16/2020      Drop-In 5

Here is a sample query:

SELECT 
    visit_date,
    strftime('%Y', visit_date) as "Year",
    strftime('%m', visit_date) as "Month",
    strftime('%d', visit_date) as "Day"
FROM
    visit_log

And this is a sample of the output.

visit_date  Year    Month   Day
06/09/2020  NULL    NULL    NULL
06/09/2020  NULL    NULL    NULL
06/16/2020  NULL    NULL    NULL
06/16/2020  NULL    NULL    NULL
06/16/2020  NULL    NULL    NULL
06/16/2020  NULL    NULL    NULL
06/16/2020  NULL    NULL    NULL

What am I missing?

Answer 1

The way your dates are formated makes it impossible to use SQLite string functions directly (SQLite wants something like YYYY-MM-DD, while you have MM/DD/YYYY).

I would recommend fixing your data in order to use a proper format for your date strings. You can do this with the following update query:

update visit_log 
set visit_date = 
    substr(visit_date, 7, 4) 
    || '-' || substr(visit_date, 1, 2) 
    || '-' || substr(visit_date, 4, 2)

In the meantime, for your current dataset, it seems simpler to use string functions here:

select
    visit_date,
    substr(visit_date, 7, 4) as "year",
    substr(visit_date, 1, 2) as "month",
    substr(visit_date, 4, 2) as "day"
from visit_log