sqlite3+DateField: field__year doesn't work.

[Jakub Wilk <django@…>]

With DATABASE_ENGINE = 'sqlite3' and the following foo/models.py:

from django.db import models

class Foo(models.Model):
        date = models.DateField()

I try:

>>> from datetime import date
>>> from foo.models import Foo
Foo(None, date(2007, 1, 1)).save()
>>> Foo.objects.all()
[<Foo: Foo object>]
>>> Foo.objects.filter(date__range = (date(2007, 1, 1), date(2007, 12, 31)))
[<Foo: Foo object>]
>>> Foo.objects.filter(date__year = 2007)
[]

[Matt McClanahan]

The query does work, however it appears that SQLite is performing an exclusive BETWEEN rather than an inclusive one. This is inconsistent with other DB systems, and is possibly a SQLite bug.

[Jakub Wilk <django@…>]

Replying to mattmcc:

The query does work, however it appears that SQLite is performing an exclusive BETWEEN rather than an inclusive one.

Nope, this is not the case. date__range also uses a BETWEEN expression but it works fine. The problem is that SQLite doesn't have a real date/datetime type.

sqlite> SELECT date('2007-01-01');
2007-01-01
sqlite> SELECT datetime('2007-01-01');
2007-01-01 00:00:00
sqlite> SELECT 1 WHERE date('2007-01-01') > date('2007-01-01');
sqlite> SELECT 1 WHERE datetime('2007-01-01') > date('2007-01-01');
1

[James Bennett]

Closing in favor of #3689, which has patches.

[anonymous]

in db/backends/sqlite3/base.py the time is incorrect.

def year_lookup_bounds(self, value):
    first = '%s-01-01'
    second = '%s-12-31 23:59:59.999999'
    return [first % value, second % value]

the correct format is '%s-12-31T23:59:59.999999' according to ​http://www.sqlite.org/lang_datefunc.html

the path of #3689 don't solve the problem

[Matthias Kestenholz]

Cannot reproduce, please provide a testcase if this is still a problem.