How to Get Day Names in SQLite

Problem:

You want to extract the name of the day of the week from a date in SQLite.

Solution 1:

To extract the day name from the date, use the strftime() function. The first parameter is the desired output and the second is the date. To extract the day name, the first parameter should be %w. It outputs a number from 0 (Sunday) to 6 (Saturday). Here’s an example:

SELECT strftime('%w','2022-01-01');

The result is 6, which corresponds to Saturday.

Discussion:

Unfortunately, SQLite has no standard method to extract a day name instead of an index. There is, however, a way to get a day name using the CASE WHEN syntax. These operators let you check every possible output value and assign a weekday name to each.

Solution 2:

Let’s extract a day name using CASE WHEN and CAST on a strftime() result.

select
  case cast (strftime('%w', '2022-01-01') as integer)
  when 0 then 'Sunday'
  when 1 then 'Monday'
  when 2 then 'Tuesday'
  when 3 then 'Wednesday'
  when 4 then 'Thursday'
  when 5 then 'Friday'
  else 'Saturday' end as weekday

The result is 'Saturday'.