Format date as day of week

I have a table which has amongst others a date column servdate.

I use the following query to get me all jobs from within the past week 开发者_开发技巧( a week starts from monday):

SELECT * FROM tb1 WHERE servdate BETWEEN date('now', 'Weekday 1', '-21 days') AND date('now')

I want the query to work exactly the same but instead to return the servdate fields as their corresponding day of the week. For example, "monday", instead of "2010-11-28".

Is this possible?

You can use an ancillary table as wallyk suggested; or you can use a case expression:

select _id, busnum, 
  case cast (strftime('%w', servdate) 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 servdayofweek
from tb1
where ...

Here a suggestion involving less typing than the CASE ... WHEN block. Here, the same is done by substring extraction:

select substr('SunMonTueWedThuFriSat', 1 + 3*strftime('%w', timestr), 3) as dow
from ...

It produces only fixed length abbreviations instead of full weekday names, though. (But sometimes that's enough.)

SELECT _id, busnum, date_format(servdate, '%a') as servdayofweek
FROM tb1
WHERE servdate BETWEEN date('now', 'Weekday 1', '-21 days') AND date('now')

The %a is the abbreviated day of the week. See the documentation for other ways to format.

edit:

Oops! Overlooked the sqlite tag. The above is for MySQL. For sqlite (documentation):

SELECT _id, busnum, strftime('%w', servdate) as servdayofweek
FROM tb1
WHERE servdate BETWEEN date('now', 'Weekday 1', '-21 days') AND date('now')

except this returns the day of week as a value 0 through 6. Maybe that's good enough?