sqlite返回为星期几 [英] sqlite return as day of week
本文介绍了sqlite返回为星期几的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表有3列:
tb1 = _id(int,prikey,autoinc),busnum(text),servdate >
我使用以下查询来获取过去一周内的所有工作(一周从星期一开始):
SELECT * FROM tb1 WHERE servdate BETWEEN date('now','Weekday 1','-21 days')AND date('now')
我希望查询的工作方式完全相同,而是返回servdate字段作为他们相应的星期几。例如星期一,而不是2010-11-28。
这是可能的吗?
解决方案
正如wallyk所建议的;或者可以使用 case
表达式:
select _id,busnum,
case cast(strftime('%w',servdate)as integer)
当0然后'星期日'
当1然后'星期一'
当2然后'星期二'
当3然后'星期三'
当4然后'星期四'
当5然后'星期五'
否'星期六'结束作为servdayofweek
从tb1
where ...
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 ...
这篇关于sqlite返回为星期几的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文