Is the '-1 month' in SQLite DATETIME function 'broken'?

Question

When using the DATETIME function to do some arithmetic, I've found the following behaviour:

select DATETIME('now', '-1 month', 'start of month')
expected: 2011-02-01 00:00:0
result: 2011-03-01 00:00:0 - Fail

select DATETIME('now', '-2 month', 'start of month')
expected: 2011-01-01 00:00:0
result: 2011-01-01 00:00:0 - Ok

This only appears to be happening since yesterday, my integration tests picked up on this. My guess is that the month that is substracted is calculated in days and that somehow the number of days in the last full month is used (not the current). The last full month, february featured 28 days, hence the result)

This is easily resolved by changing the order of input to the DATETIME function, like so: DATETIME('now', 'start of month', '-1 month'), but still it is tricky behaviour and likely to result in bugs that get unnoticed.

I am using System.Data.SQLite.DLL / version 1.0.66.0 / Apr 18, 2010

Anybody else found this behaviour? Is this a (known) bug? Or am I 'doing it wrong'?

Answer 1

now minus one month = 30th of February = 2nd of March. Then start of month gives you the start of March. This is probably not the behaviour anyone ever wants, but it's what the documentation describes: go to http://www.sqlite.org/lang_datefunc.html and search for "works by rendering".

Answer 2

Instead of:

SELECT datetime('now','-1 month','start of month');

Have you tried:

SELECT datetime('now','start of month','-1 month');

I think the second will always give you the first day of the previous month. The former will sometimes give you the first day of the current month if there are more days in the current month than in the previous month.