Sqlite calculate difference in days between YYYYMMDD date formats in Query

Question

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.

Answer 1

SQLite doesn't actually have a native storage class for timestamps / dates. It stores these values as NUMERIC or TEXT typed values depending on input format. Date manipulation is done using the builtin date/time functions, which know how to convert inputs from the other formats.

You will probably need to cast your date fields to a number of days, using the julianday function to perform the subtraction, otherwise they'll likely be typed as strings.

SELECT * FROM table WHERE (julianday(rdate)-julianday(tdate)) <= 30  ;

Here is the documentation about SQLite data types

Your query is valid, because the subtraction operator implicitly casts its operands to NUMERIC. What these numerics resolve to will depend on the actual format of the date values you have stored in your fields. You could see this by using a query that explicitly lists these casts.

SELECT rdate,tdate,
       cast(rdate AS NUMERIC), cast(tdate AS NUMERIC),
       rdate - tdate 
FROM table ;

Which should make it clear what is happening in your original query.