Sqlite在Query中计算YYYYMMDD日期格式之间的天数差异 [英] Sqlite calculate difference in days between YYYYMMDD date formats in Query

问题描述

我已经为某些病人记录以YYYYMMDD格式存储了治疗日期（tdate）和收到日期（rdate）日期。我希望查询rdate - tdate的结果小于30的记录。

我的尝试是

  SELECT * FROM table WHERE（rdate  -  tdate）< = 30; 
  

不知何故这是一个有效的查询，但结果是别的。让我知道如果你有一个解决方案。

解决方案

我能够这样工作的答案@cms写，这是什么工作

  SELECT *，（julianday（substr（rdate`，1`，4）|| - || substr（rdate`，5`，2）|| - || substr（rdate`，7`，2）） -  julianday（substr（tdate`，1`，4）|| - || substr tdate`，5`，2）|| - || substr（tdate`，7`，2）））'差'FROM表WHERE差值<= 30.0 
  / pre> 
 
 
它像charm一样工作
 
I have stored treatment date (tdate) and received date (rdate) dates in YYYYMMDD format for some patient records. I wish to query records that the result of rdate - tdate is less than 30.

My attempt was
SELECT * FROM table WHERE (rdate - tdate) <= 30;
Somehow this is a valid query but the results are something else. Let me know if you have a solution.
解决方案
I was able so work with the answer @cms wrote and this is what worked for me.
SELECT *,(julianday(substr(rdate`,1`,4)||"-"||substr(rdate`,5`,2)||"-"||substr(rdate`,7`,2))-julianday(substr(tdate`,1`,4)||"-"||substr(tdate`,5`,2)||"-"||substr(tdate`,7`,2)))'difference' FROM table WHERE difference <= 30.0
It worked like charm

                        
这篇关于Sqlite在Query中计算YYYYMMDD日期格式之间的天数差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！