Converting a String to Datetime in Python

Question

I'm having some issues searching for date in my sqlite database (using peewee as ORM). If a user enters a specific date in format like 03/31/2017, nothing is happening even though that is a valid date in my database. I'm pretty sure this is some sort of formatting problem, but i'm using same format across the search input and the database query--so not sure what problem might be.

from collections import OrderedDict
import datetime
import os

from peewee import *

db = SqliteDatabase('wlg.db')

fmt = '%m/%d/%Y'


class Entry(Model):
    notes = TextField()
    name = TextField()
    task = TextField()
    minutes = IntegerField()
    date = DateField(formats=[fmt])

    class Meta:
        database = db


def initialize():
    """Create database and table if they don't exist"""
    db.connect()
    db.create_tables([Entry], safe=True)


def clear():
    os.system('cls' if os.name == 'nt' else 'clear')

def menu_loop():
    """Show the menu"""
    choice = None

    while choice != 'q':
        clear()
        print("Enter 'q' to quit")
        for key, value in menu.items():
            print('{}) {}'.format(key, value.__doc__))
        choice = input('Action: ').lower().strip()

        if choice in menu:
            clear()
            menu[choice]()


def add_entry():
    """Add an entry"""
    print("Enter your name or 'm' to return to main menu.")
    while True:
        name = input('> ')
        if name.lower().strip() != 'm':
            task = input("What task did you do? ")
            minutes = input("How many minutes did it take? ")
            notes = input("Please enter any notes about the task: ")
            date = datetime.date.today().strftime(fmt)
            Entry.create(name=name, task=task, minutes=minutes, notes=notes, date=date)
            input("Hit Enter/Return to go back and add a task or view previous entries.")
            break
        else:
            menu_loop()


def view_entries(search_employee=None, search_date=None, search_time=None, search_term=None):
    """View previous entries"""
    entries = Entry.select().order_by(Entry.date.desc())

    if search_employee:
        entries = entries.where(Entry.name.contains(search_employee))

    elif search_date:
        dt = datetime.datetime.strptime(search_date, fmt)
        entries = entries.where(Entry.date == dt)

    elif search_time:
        entries = entries.where(Entry.minutes == int(search_time))

    elif search_term:
        entries = entries.where((Entry.task.contains(search_term))|(Entry.notes.contains(search_term)))

    for entry in entries:
        date = entry.date.strftime(fmt)
        print(date)
        print('='*len(date))
        print("Name: " + entry.name)
        print("Task: " + entry.task)
        print("Minutes Taken: " + str(entry.minutes))
        print("Task Notes: " + entry.notes)
        print("Date: " + date)
        print('n) next entry')
        print('q) return to main menu')

        next_action = input('Action: [Nq] ').lower().strip()
        if next_action == 'q':
            break


def search_by_employee():
    view_entries(search_employee=input('Search query: '))


def search_by_date():
    view_entries(search_date=input('Enter Date in Format(mm/dd/yyyy)): '))


def search_by_time():
    view_entries(search_time=input('Search query: '))
    # while True:
    #     search_time = (input('Search query: '))
    #     try:
    #         search_time = int(search_time)
    #         view_entries(search_time)
    #     except ValueError:
    #         print("Not a valid entry. Please try again")


def search_by_term():
    view_entries(search_term = input('Search query: '))


def search_entries():
    """Search previous entries"""
    # view_entries(input('Search query: '))
    while True:
        lookup = input("Lookup by Employee(E), Date(D), Time(T) or Search Term(S): ")
        lookup.lower()

        if lookup == 'e':
            search_by_employee()
            break
        elif lookup == 'd':
            search_by_date()
            break
        elif lookup == 't':
            search_by_time()
            break
        elif lookup == 's':
            search_by_term()
            break
        else:
            print("Sorry invalid option. Please try again")


def delete_entry(entry):
    """Delete an entry"""
    pass

menu = OrderedDict([
    ('a', add_entry),
    ('v', view_entries),
    ('s', search_entries)
])

if __name__ == '__main__':
    initialize()
    menu_loop()

Answer 1

03/31/2017 is not a valid date here (France).

If you want locale-specific date handling, I believe the python datetime supports it.

Locale date formatting in Python

However, if your application has international users, you should be very, very careful, because 01/02/2017 is Feb. 1st or Jan. 2nd depending on the part of the world your users come from.

I find text dates much better for display. For example, in French it would be "1er Février" but "Feb. 1" is non-ambiguous and absolutely fine if the website is set to English.

However, displaying "02/01" is asking for trouble.

//End rant.

Hypotheses that may explain your issue:

Using datetime.datetime, but your column is a date without time. Something could go wrong in the conversion. Using the proper type is always a good idea.

Add print() to make sure your date is converted into a python date object properly.

You declare:

date = DateField(formats=[fmt])

Since there is a "fmt" specification this makes me think that peewee performs some kind of internal conversion between SQLite internal date format (ISO YYYY-MM-DD), and possibly requires a "mm/dd/yyyy" string instead of a date object. You should check this.

Also check if your ORM peewee has a way to display the SQL queries it issues. This should make the mistakes obvious.