SQLite, leap year check, and day counting

Question

I am trying to write some sqlite code that will check if the date range include February 29th and if so, divide the total number of days elapsed by 366. Otherwise, divide by 365. I've only included one of the two queries as fixing one essentially fixes the other.

Thank you for your help.

 UPDATE table SET "Date Adjust" = (strftime('%Y-%m-%d',"End Date") - 
    strftime('%Y-%m-%d',"Start Date"))/366
    WHERE "2020-02-29" BETWEEN strftime('%Y-%m-%d',"Start Date") AND strftime('%Y-%m-%d',"End Date")

Answer 1

If you want to get the difference in days between 2 dates you should not subtract the result of strftime().
The function strftime() returns a formatted date as a string.

You can use the function julianday():

UPDATE tablename 
SET "Date Adjust" = 
  (julianday('%Y-%m-%d',"End Date") - julianday('%Y-%m-%d',"Start Date")) / 
  (365 + ("2020-02-29" BETWEEN strftime('%Y-%m-%d', "Start Date") AND strftime('%Y-%m-%d', "End Date")))

The value returned by julianday() is a floating point number, so if you want the result as an integer you may round it with the function ROUND().

For this statement to work your dates must be in the only valid date format for SQLite which is YYYY-MM-DD.

The above statement will work for both cases: leap year or not.