Storing date value into sqlite table in python3 stores integer value

I’m stuck with this bug and on how to solve it.

Background: I have built a ‘Paycheck’ table in SQLite that has a date attribute, net pay attribute, and a gross pay attribute. I have set the date attribute to a date datatype in SQLite, and set the net and gross attribute to an integer datatype.

The problem: I’m experiencing a problem where when I try to insert a paycheck record, the date attribute will be stored as “1994”. The thing is, while I was making the function to store a paycheck record, I read online that the date format for the date datatype in SQLite was yyyy-mm-dd, and I made sure that I’d insert the date in that format, but the code would run fine but in the database, every time I try to add a record it would state “1994” for the date.

Here’s all the associated code:

import sqlite3
import datetime


def date_sql_format():
    """return date in sql DATE format"""
    return str(datetime.datetime.now().strftime("%Y-%m-%d"))


def execute(command: str):
    """Take a sql query and execute it on the database."""
    connect = sqlite3.connect("db.sqlite")
    cursor = connect.cursor()
    cursor.execute(command)
    connect.commit()
    connect.close()


def add_paycheck(date: str, net: float, gross: float):
    """Add a paycheck record to the database."""
    template = f"{date}, {net}, {gross}"
    execute(f"INSERT INTO Paycheck (Date, Net, Gross) VALUES ({template})")

Answer

Evaluate this 2020-04-22 as an arithmetic expression. The answer is 1994. That’s where that number is coming from.

sqlite doesn’t have a datatype date internally. If you look at the Affinity Name Examples section of the sqlite datatypes doc, that typename DATE from a create table statement results in a NUMERIC affinity. So it does the math.

Change the datatype of the date column to TEXT. And you might want to keep the sqlite date and time functions doc handy.