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

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 - O开发者_StackOverflow中文版k

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'?

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".

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.

I'm pretty sure that's working as documented.

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.

I'd expect the change before normalizing to be Feb 30, which isn't a valid date.