How do I write sqlite insert query to add date in correct format?

Question

0.00/5 (No votes)

See more:

This is a straight SQLITE question. I have the following basic table:

SQL

CREATE TABLE DateTest (
    id   INTEGER  PRIMARY KEY AUTOINCREMENT
                  UNIQUE
                  NOT NULL,
    Date DATETIME
);

I want to insert the date (10/31/2021) into the Date field as:

2021-10-31 00:00:00

Am I on the right track here, or am I just totally offbase?:

SQL

INSERT INTO DateTest VALUES (strftime('%Y/%m/%d', 2021/10/31));

Thanks for your help!

What I have tried:

Inordinate amount of time researching online, but knowledge is limited, I am just starting.

Posted 15-Feb-22 14:12pm

Blue Ocean

Updated 15-Feb-22 21:45pm

Add a Solution

2 solutions

Solution 2

See Datatypes In SQLite[^] and Date And Time Functions[^] for details about how to store dates and times, and how to display them.

Posted 15-Feb-22 21:45pm

Richard MacCutchan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Luc Pattyn · Accepted Answer · 2022-02-15T15:03:00

That is not OK, for several reasons:

1. a good INSERT statement lists the fields and the values; it may work without explicitly naming the fields but then any change to your table structure is bound to break your code.

2. Date literals (as well as datetime/timestamp/string literals) need quotes in SQL

3. IMO you don't need strftime; besides, this function has been DEPRECATED as of PHP 8.1.0
Just try ... VALUES ('2021/10/31')

Finally, the safe way to execute database operations is by using parameterized queries:
Parameters - Microsoft.Data.Sqlite | Microsoft Docs[^]

Please use them. Always. Advantages:
- no more quoting issues
- no more date formatting issues
- automatic protection against SQL Injection Attacks

By not using a parameterized query, you have to convert your date to a string, and then the database has to convert it back to a date. There is a double risk of errors about date formatting conventions, which gets totally avoided when the date is passed as a date, not as a string.

:)