id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24176	Incorrect SQL text when searching in SQLite for datetime values with milliseconds	Alexandr Zarubkin	nobody	"I have SQLite database where certain column in table is created as TIMESTAMP. The database is created not by Django, but by SymmetricDS, a DB replication software. The database is replicated from Microsoft SQL Server 2005, where it has datetime type. In my Django application, the model has DateTimeField for that column.
{{{#!python
class Calendar(models.Model):
    day_id = models.IntegerField(primary_key=True)
    day_date = models.DateTimeField()
}}}
The table contains a row with day_date equal to '2015-01-18 00:00:00.000'.

If I try to search for that row with
{{{#!python
day = Calendar.objects.filter(day_date=date(2015,1,18))
}}}
I get SQL with the following WHERE clause:
{{{#!sql
WHERE day_date = '2015-01-18 00:00:00'
}}}
, i.e. it doesn't contain milliseconds part. As a consequence, the row is not found. According to http://www.sqlite.org/datatype3.html, the text representation of datetime values in SQLite should have milliseconds part.

In MS SQL it works fine (I use django-pyodbc-azure driver).

The workaround is:
{{{#!python
dt = datetime(2015,1,18) # datetime, not date
day = Calendar.objects.filter(day_date__lt=day+timedelta(seconds=1),
                              day_date__gt=day-timedelta(seconds=1))
}}}"	Bug	closed	Database layer (models, ORM)	1.7	Normal	fixed			Accepted	0	0	0	0	0	0