How to store and query dates in SQLite - using DB browser for Sqlite

Question

Not sure which data type and formatting to use when storing dates in my tables in DB browser for SQLite. Also not sure how to format the SQL queries when retrieving the data. For instance I've created a films database with an attribute for Release (as text data type), I want to be able to find films between two dates.

table : films +-------+------------+-------------+ | id | title | Release |
+-------+------------+-------------+ | 1 | Star Wars | 2000-01-01 |
| 2 | Star Trek | 2010-01-01 |

tried:

SELECT * FROM tblFilms WHERE Release BETWEEN (2000-01-01 AND 2010-01-01)

This does not return any values

Please revise your question according to these guidelines meta.stackoverflow.com/questions/271055 — PChemGuy
– PChemGuy, Commented Sep 17, 2022 at 18:51
Your query does not return results because you do not enclose the 2 dates inside single quotes. — forpas
– forpas, Commented Sep 18, 2022 at 8:25
Also remove the parentheses: SELECT * FROM tblFilms WHERE Release BETWEEN '2000-01-01' AND '2010-01-01' — forpas
– forpas, Commented Sep 18, 2022 at 8:28
Your first operation selected dates between 1998 and 2008, and probably not even interpreted these as a year. The dates became calculations. This is why date values should be quoted. — MyICQ
– MyICQ, Commented Sep 18, 2022 at 9:56

MyICQ · Accepted Answer · 2022-09-17 22:01:14Z

1

It's indiffernent if the following is from DB Browser for SQLite, or the SQLite terminal itself. The software is just a frontend.

Storing dates can be done in principle in any form, as SQLite is not typed, but natively SQLite works with one of these formats:

text in a subset of the ISO-8601 format: 2022-09-17 23:34:08
Julian day : 2459840.40688294
Seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp). : 1663451396

See documentation for SQLite dates.

Queries on dates, and limiting results, is explained with examples here.

Formatting results can be done using strftime:

select strftime("%m/%d/%Y", "2022-09-17") as 'US DATE'

returns 09/17/2022

answered Sep 17, 2022 at 22:01

MyICQ

1,1761 gold badge12 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to store and query dates in SQLite - using DB browser for Sqlite

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related