Sqlite 将字符串转换为日期
我将日期作为字符串存储在 sqlite 数据库中，例如28/11/2010".我想将字符串转换为日期.
I have date stored as string in an sqlite database like "28/11/2010". I want to convert the string to date.
特别是我必须在两个日期之间转换大量字符串日期.
Specifically I have to convert lots of string dates between two dates.
在 postgresql 中，我使用 to_date('30/11/2010','dd/MM/yyyy')，我如何用 sqlite 做同样的事情?
In postgresql, I use to_date('30/11/2010','dd/MM/yyyy'), how can I do the same thing with sqlite?
像这样:
SELECT * FROM table
WHERE to_date(column,'dd/MM/yyyy')
BETWEEN to_date('01/11/2010','dd/MM/yyyy')
AND to_date('30/11/2010','dd/MM/yyyy')
推荐答案
As Sqlite 没有日期类型您将需要进行字符串比较以实现此目的.为此，您需要颠倒顺序 - 例如从 dd/MM/yyyy 到 yyyyMMdd，使用类似
As Sqlite doesn't have a date type you will need to do string comparison to achieve this. For that to work you need to reverse the order - eg from dd/MM/yyyy to yyyyMMdd, using something like
where substr(column,7)||substr(column,4,2)||substr(column,1,2)
between '20101101' and '20101130'
相关文章