How to Correctly Insert Datetime Values into an SQLite Database?

Inserting Datetime Values into SQLite Databases

When attempting to insert a datetime value into a SQLite database, you may face complications. One common error is receiving a message indicating "Unable to read data" when trying to retrieve the value.

This error is typically encountered when the datetime value is not formatted correctly. The expected format for datetime values in SQLite databases is 'yyyy-MM-dd HH:mm:ss'. In other words, the format should include the year, month, day, hour, minute, and second.

To insert a datetime value correctly, you should use the following format:

'2007-01-01 10:00:00'

For example:

create table myTable (name varchar(25), myDate DATETIME);
insert into myTable (name, myDate) values ('fred', '2009-01-01 13:22:15');

To avoid formatting errors and enhance security, it's recommended to use parameterized queries. Parameterized queries replace literal values with placeholders, allowing you to dynamically set the values during runtime without worrying about formatting.

Here's an example of a parameterized query to insert a datetime value:

prepared_statement = connection.prepare("INSERT INTO myTable (name, myDate) VALUES (?, ?)")
prepared_statement.bind([('fred'), ('2009-01-01 13:22:15')])
prepared_statement.execute()

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