How to Successfully Insert Datetime Values into SQLite Databases?

Inserting Datetime Values into SQLite Databases

When attempting to insert a datetime value into a SQLite database, users may encounter retrieval errors such as "Unable to read data." To resolve this issue and ensure successful insertion and retrieval, adhere to the following guidelines:

Datetime Value Format:

The datetime value must be formatted in the following ISO 8601 standard format:

yyyy-MM-dd HH:mm:ss

This means the format should be "year-month-day hour:minute:second," such as '2007-01-01 10:00:00'.

Syntax for Inserting Datetime Value:

When using the INSERT statement, enclose the datetime value in single quotes, as seen below:

INSERT INTO myTable (name, myDate) VALUES ('fred', '2009-01-01 13:22:15');

Use of Parameterized Queries:

To avoid potential formatting issues, consider using parameterized queries. This allows you to pass the datetime value as a parameter, allowing the database engine to handle the formatting automatically.

For example, using SQLite's execute() method with a parameterized query:

import sqlite3

conn = sqlite3.connect('my_database.sqlite')
cursor = conn.cursor()

sql = "INSERT INTO myTable (name, myDate) VALUES (?, ?)"
data = ('fred', '2009-01-01 13:22:15')
cursor.execute(sql, data)

The above is the detailed content of How to Successfully Insert Datetime Values into SQLite Databases?. For more information, please follow other related articles on the PHP Chinese website!