Search code examples
sqlitedatedate-arithmeticstrftime

Full months difference

create table mytable (id integer not null,date_start TEXT,date_end TEXT,wanted_full_month INTEGER); 
insert into mytable (id, date_start, date_end, wanted_full_month)
values (1, '1992-09-15', '1992-11-14',1); /* Incomplete second month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (2, '1992-09-15', '1992-11-15',2); /* Complete second month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (3, '1992-09-15', '1992-10-14',0); /* Incomplete first month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (4, '1992-01-31', '1992-02-29',1);
/* It's the end of the month of date_end and the end of the month of date_start,
we take it as a complete month */
insert into mytable (id, date_start, date_end, wanted_full_month)
values (5, '1992-01-30', '1992-02-29',1);
/* It's the end of the month of date_end, it couldn't go longer,
we take it as a complete month */
SELECT *,floor((julianday(date_end) - julianday(date_start))/30) as wrong_full_months from mytable; as wrong_full_months from mytable;

results

How can I have a function like date_sub from DuckDB (documentation, source code) using SQLite? That is, getting the (irregular) difference of months like the column wanted_full_months (not a multiple of 30 days like in my example).

Solution

  • If you want a solution with SQLite code:

    SELECT *,
       strftime('%Y', date_end, 'start of month', '-1 day') * 12 +
       strftime('%m', date_end, 'start of month', '-1 day') -
       strftime('%Y', date_start) * 12 -
       strftime('%m', date_start) +
       (strftime('%d', date_end, '+1 day') = '01'
        OR 
        strftime('%d', date_end) >= strftime('%d', date_start)
       ) full_month
FROM mytable;

    See the demo.