Hello,

I am trying to get records between two dates, the date field is the TEXT data type.

I got this working for dates in the same month, this will return values for each day:
"SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN '27/03/2023' AND '31/03/2023'"

This does not work for separate months though, if I do it will return nothing:
"SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN '27/03/2023' AND '01/04/2023'"

Should I be doing this another way?

Thanks.

In article <145ca78b-35de-4641-b895-3c1eeb571139n@googlegroups.com>,
Shaun Kulesa <shaunkulesa@gmail.com> wrote:
>Hello,
>
>I am trying to get records between two dates, the date field is the TEXT
>data type.
>
>I got this working for dates in the same month, this will return values
>for each day:
>"SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN
>'27/03/2023' AND '31/03/2023'"
>
>This does not work for separate months though, if I do it will return nothing:
>"SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN
>'27/03/2023' AND '01/04/2023'"
>
>Should I be doing this another way?
>
>Thanks.

Maybe something with unixepoch()?

SELECT * FROM customer_orders WHERE stock_id=? AND
date > unixepoch('27/03/2023') AND date < unixepoch('31/03/2023')

(I don't have sqllite, so I have not actually tried that).
--
columbiaclosings.com
What's not in Columbia anymore..

Sorry, I couldn't get that to work.

Shaun Kulesa <shaunkulesa@gmail.com> wrote:
> Hello,
>
> I am trying to get records between two dates, the date field is the
> TEXT data type.
>
> I got this working for dates in the same month, this will return
> values for each day: "SELECT * FROM customer_orders WHERE stock_id=?
> AND date BETWEEN '27/03/2023' AND '31/03/2023'"
>
> This does not work for separate months though, if I do it will return
> nothing: "SELECT * FROM customer_orders WHERE stock_id=? AND date
> BETWEEN '27/03/2023' AND '01/04/2023'"
>
> Should I be doing this another way?

Do you have freedom to 'redo' the database table?

If yes, then change the date column to be one of two items:

1) If you want to stick with 'text' colums, use an ISO8601 style date:
YYYY MM DD (i.e. YYYY-MM-DD or YYYY/MM/DD). This format has the
advantage that the ASCII text sort order of the strings is *also* a
sort in date order. This will let your 'between' query actually find
dates that are 'between' those values

2) Switch the dates to be unix epoch integer values, then a normal
integer range will find 'between' values.

And, note that for 'display' purposes, you can display whatever format
a user wants externally, independent of the format you use inside the
DB.

I totally agree with your last statement about displaying it in whatever format on the GUI side.

I stuck with the TEXT and changed the dates in the database to be YYYY/MM/DD, I must of missed something because if I do the query with BETWEEN '2023/03/27' AND '2023/03/31' (or '2023/04/01') it will return nothing.

SELECT * FROM customer_orders WHERE date BETWEEN '2023/03/27' AND '2023/03/31'

I'm going to attach the database and a image of the contents so you can have a reference:
https://drive.google.com/drive/folders/16sMDU4_DDbCbaaEJRqhQMQQvgvxnZ6xk?usp=sharing

Thank you for your help so far.

On 4/6/23 12:00 PM, Shaun Kulesa wrote:
> Hello,
>
> I am trying to get records between two dates, the date field is the TEXT data type.
>
> I got this working for dates in the same month, this will return values for each day:
> "SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN '27/03/2023' AND '31/03/2023'"
>
> This does not work for separate months though, if I do it will return nothing:
> "SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN '27/03/2023' AND '01/04/2023'"
>
> Should I be doing this another way?
>
> Thanks.

Since the field is TEXT, the BETWEEN operation will work on the data as
TEXT, not trying to interprete it as a date.

You either need to process the date field by using a function to convert
the date to something that actually works as a date, or store the dates
in a format that work correctly, like YYYY-MM-DD

On 4/6/2023 1:26 PM, Shaun Kulesa wrote:
> This does not work for separate months though, if I do it will return nothing:
> "SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN '27/03/2023' AND '01/04/2023'"

This is because you are comparing strings "27/..." and "01/..."

> I stuck with the TEXT and changed the dates in the database to be YYYY/MM/DD, I must of missed something because if I do the query with BETWEEN '2023/03/27' AND '2023/03/31' (or '2023/04/01') it will return nothing.
>
> SELECT * FROM customer_orders WHERE date BETWEEN '2023/03/27' AND '2023/03/31'
>

You can use date formatting functions and then do your sql or you could
stick to a format that should work implicitly - like this which is safe
for both string and date comparisons: 'YYYY-MM-DD' will work in iether case.

saitology9 <saitology9@gmail.com> wrote:
> On 4/6/2023 1:26 PM, Shaun Kulesa wrote:
>> This does not work for separate months though, if I do it will return nothing:
>> "SELECT * FROM customer_orders WHERE stock_id=? AND date BETWEEN '27/03/2023' AND '01/04/2023'"
>
> This is because you are comparing strings "27/..." and "01/..."

And also because your db now contains YYYY-MM-DD and you are using / in
your between.

From your DB:

sqlite> select * from customer_orders ;
id stock_id order_cost quantity shipped date
-- -------- ---------- -------- ------- ----------
18 2 11.9 7 0 2023-04-01
19 2 17 10 0 2023-03-27
20 2 25.5 15 0 2023-03-28
sqlite> select * from customer_orders where date between '2023-03-27' and '2023-04-01';
id stock_id order_cost quantity shipped date
-- -------- ---------- -------- ------- ----------
18 2 11.9 7 0 2023-04-01
19 2 17 10 0 2023-03-27
20 2 25.5 15 0 2023-03-28

If you format the dates in the query the same way as the DB now stores
them, and put the smaller date first in the 'between' query, it works.

Yes I see, I forgot to switch to using the dash.

It's been a long day, thank you everyone for your help.