sqlite return as day of week
I have 1 table which has 3 columns:
tb1 = _id(int,prikey,autoinc) , busnum(text) , servdate(date)
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 ...