Conversion failed when converting date and/or time from character string.

[kevin-olbrich]

I am unable to filter on a datetime colum (u_sentinstant):

Traceback (most recent call last):
  File "/usr/lib/python3.5/site-packages/django_pyodbc/base.py", line 489, in execute
    return self.cursor.execute(sql, params)
pyodbc.DataError: ('22007', '[22007] [FreeTDS][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

Code:

class Command(BaseCommand):
    help = 'Sends email notifications'

    def handle(self, *args, **options):
        now = datetime.now()

        all_open_port = EmailTable.objects.using('prod').filter(u_sentinstant__gte = datetime.today())

        for p in all_open_port:
            print(p.u_sentinstant)

        self.stdout.write(self.style.SUCCESS('Successfully finished.'))

Tested on Fedora 25 using FreeTDS (TDS_Version 7.2 and 7.3) and unixODBC and Windows 10. Backend is SQL SRV 2014.

all_open_port.query:

SELECT `emailtable`.`Code`, `emailtable`.`Name`, `emailtable`.`U_portjobid`, `emailtable`.`U_notifrtype`, `emailtable`.`U_address`, `emailtable`.`U_sentinstant`, `emailtable`.`U_sentreminder`, `emailtable`.`U_sentportdone` FROM `emailtable` WHERE `emailtable`.`U_sentinstant` >= 2017-06-03 14:24:30.137877

It would work if milliseconds are removed and the string is in single quotes.

[kevin-olbrich]

Seems like... https://github.com/michiya/django-pyodbc-azure ... has better support for latest Django and SQL 2014 / 2016.

[variable]

I had the exact error but was caused by date format, I re-arranged the dateformat from 'dd/mm/yyyy' to 'yyyy-mm-dd' has fixed the problem. https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i