How can I make a query with contains over date/datetime fields

Question

Im trying to use the datatables jQuery plugin with date/datetime fields. and when you make the query that contains those field generates a MySQL Warning when you try to use __contains in the queryset. I've detected that if you try to make queries over Year, Month and Day the warning doesn't appears.

There's another way to search in a date/datetime field without raise that warning or how could I make a query over a year/month/day in a date/datetime field.

Here is an example:

Model.objects.filter(Date__contains="2014")

Which gives this warning:

Warning: Incorrect date value: '%2014%' for column 'Fecha' at row 1

I'd like to make a query like this:

SELECT * FROM Model WHERE YEAR(Date) LIKE "%4%" OR MONTH(Date) LIKE "%4%" OR DAY(Date) LIKE "%4%";

https://docs.djangoproject.com/en/dev/ref/models/querysets/#year — dm03514, May 09 '14 at 17:38
@MikeVelazco cool, I tried that, but for me it doesn't give me exactly results, I mean sometimes it had weird behaviors, thanks! — Vinicio Ajuchan, Aug 10 '16 at 19:59

score 2 · Accepted Answer · answered May 12 '14 at 14:28

2

The best solution that I´ve found is searching by a Regex

Model.objects.filter(Date__regex="4")

answered May 12 '14 at 14:28

MikeVelazco

885
2
13
30

score 0 · Answer 2 · answered May 10 '14 at 12:38

0

Model.objects.filter(field_name__year=2014)

answered May 10 '14 at 12:38

Pradeep Agnihotri

349
2
12

1

if you want to search for a number in any position of the date as it were a string, this doesn´t work. This only filter for a year given. – MikeVelazco May 12 '14 at 14:22

How can I make a query with contains over date/datetime fields

2 Answers2