CODEWITHSUNDEEP
sqlsqlite
Zombian
Zombian

Reputation: 1435

Why does this SQLite query select all dates, even those outside of the criteria?

I am running sqlite to select data between two ranges for a sales report. To select the data from between two dates I use the following statement:

SELECT * FROM test WHERE date BETWEEN "11/1/2011" AND "11/8/2011";

This statement grabs all the dates even those outside the criteria. The date format you see entered is in the same format that I get back. I'm not sure what's wrong.

Upvotes: 84

Views: 143336

Answers (9)

Nikhil S Marathe
Nikhil S Marathe

Reputation: 771

Put the variable in the Where Condition and parse both dates using 'BETWEEN':

SELECT * FROM emp_master

-> if you have date formate like dd/mm/yyyy simple then,

   WHERE joined_date BETWEEN '01/03/2021' AND '01/09/2021';

-> and if you have date formate like yyyy/mm/dd then,

   WHERE joined_date BETWEEN '2021/03/01' AND '2021/09/01';

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60498

SQLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn't in that format, it is probably treating your "dates" as strings.

Upvotes: 118

Pepik
Pepik

Reputation: 343

Let's say you are preparing data for some report. Then the whole ordeal will look similar to this.

--add column with date in ISO 8601 
ALTER TABLE sometable ADD COLUMN DateInISO8601;

--update the date from US date to ISO8601 date 
UPDATE sometable 
SET DateInISO8601 = substr([DateInUSformat],length([DateInUSformat])+1, -4) 
  || '-' || 
  substr('00' || [DateInUSformat],instr('00' || [DateInUSformat],'/'),-2)  
  || '-' || 
  substr('00' || rtrim(substr([DateInUSformat],instr([DateInUSformat],'/')+1,2),'/'),-2,2);

SELECT DateInISO8601 
FROM sometable 
WHERE DateInISO8601 BETWEEN '2022-02-02' AND '2022-02-22';

You can of course do all that on the fly, but if you have the choice -- don't. Use the ISO date by default and convert it on the way in and out to SQLite DB.

Upvotes: 1

TROUZINE Abderrezaq
TROUZINE Abderrezaq

Reputation: 478

SELECT *
FROM TableName
WHERE julianday(substr(date,7)||'-'||substr(date,4,2)||'-'||substr(date,1,2)) BETWEEN julianday('2011-01-11') AND julianday('2011-08-11')

Note that I use the format: dd/mm/yyyy. If you use d/m/yyyy, Change in substr().

Upvotes: 5

Captain Man
Captain Man

Reputation: 7695

SQLite does not have a concept of dates. It only knows them as text. When you do this in SQLite you're actually doing string comparisons. You can read more from the official documentation.

When two TEXT values are compared an appropriate collating sequence is used to determine the result.

Any numeric (i.e., not using words like 'May') format for dates that is padded and in order from biggest field to smallest field will work. "2021-05-07" (May 7th) comes before "2021-05-09" (May 9th). So if you use "yyyy-mm-dd" format then you'll be set. "yyyy/mm/dd" and "yyyymmdd" work just fine too. (For a better phrasing on "sortable" date formats check out RFC 3339 section 5.1.)

A reason to use "yyyy-mm-dd" format is because that's the format that SQLite's builtin date uses.

Upvotes: 3

adudakov
adudakov

Reputation: 222

Or you can cast your string to Date format with date function. Even the date is stored as TEXT in the DB. Like this (the most workable variant):

SELECT * FROM test WHERE date(date) 
BETWEEN date('2011-01-11') AND date('2011-08-11')

Upvotes: 4

Utku Yıldırım
Utku Yıldırım

Reputation: 2277

Change your data to that formats to use sqlite datetime formats.

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

SELECT * FROM test WHERE date BETWEEN '2011-01-11' AND '2011-08-11'

Upvotes: 41

TROUZINE Abderrezaq
TROUZINE Abderrezaq

Reputation: 478

Special thanks to Jeff and vapcguy your interactivity is really encouraging.

Here is a more complex statement that is useful when the length between '/' is unknown:: 

SELECT * FROM tableName
WHERE julianday(
    substr(substr(date, instr(date, '/')+1), instr(substr(date, instr(date, '/')+1), '/')+1)
||'-'||
    case when length(
    substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1),'/')-1)
    )=2
    then
    substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1), '/')-1)
    else
    '0'||substr(date, instr(date, '/')+1, instr(substr(date, instr(date, '/')+1), '/')-1)
    end
||'-'||
    case when length(substr(date,1, instr(date, '/')-1 )) =2
    then substr(date,1, instr(date, '/')-1 )
    else
    '0'||substr(date,1, instr(date, '/')-1 )
    end
) BETWEEN julianday('2015-03-14') AND julianday('2015-03-16')

Upvotes: 2

Andrei Drynov
Andrei Drynov

Reputation: 8592

One more way to select between dates in SQLite is to use the powerful strftime function:

SELECT * FROM test WHERE strftime('%Y-%m-%d', date) BETWEEN "11-01-2011" AND "11-08-2011"

These are equivalent according to https://sqlite.org/lang_datefunc.html:

date(...)

strftime('%Y-%m-%d', ...)

but if you want more choice, you have it.

Upvotes: 19

Related Questions