Query/Sqlite: AddMonths and AddTicks translation may lead to small result discrepancies vs l2o

[maumar]

Tests failing result verification on sql lite:
Select_expression_datetime_add_ticks
Select_expression_datetime_add_month

[maumar]

31/10 + 1 month results in 30/11 but on sqlite we translate to 1/12. Moreover, 31/1 + 1 month = 28/2 or 29/2, depending on leap year which might be even more problematic

[roji]

tl;dr Sqlite is indeed the odd one out compared to everything else I'm seeing; this is documented as follows:

Note that "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.

This seems to correspond to what Jon Skeet describes as "Work out how far we've overshot, and apply that to the next month", where as everyone else does "Round down to the end of the month", see this article for this fascinating subject. So this should be closed as by design (probably unskip the tests and override to pass).

Results:

PostgreSQL

SELECT '2020-10-31'::date + '1 month'::interval; -- 2020-11-30
SELECT '2020-01-31'::date + '1 month'::interval; -- 2020-02-29

SQL Server

SELECT DATEADD(month, 1, '2020-10-31'); -- 2020-11-30
SELECT DATEADD(month, 1, '2020-01-31'); -- 2020-02-29

Sqlite

SELECT DATE('2020-10-31','+1 months'); -- 2020-12-01
SELECT strftime('%Y-%m-%d', '2020-10-31', '1 months'); -- 2020-12-01

SELECT DATE('2020-01-31','+1 months'); -- 2020-03-02
SELECT strftime('%Y-%m-%d', '2020-01-31', '1 months'); -- 2020-03-02

Nodatime

Console.WriteLine(new LocalDate(2020, 10, 31) + Period.FromMonths(1)); // 2020-11-30
Console.WriteLine(new LocalDate(2020, 1, 31) + Period.FromMonths(1)); // 2020-02-29

.NET DateTime

Console.WriteLine(new DateTime(2020, 10, 31).AddMonths(1)); // 2020-11-30
Console.WriteLine(new DateTime(2020, 1, 31).AddMonths(1)); // 2020-02-29