Shaun Kulesa <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Sorry, I couldn't get that to work.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

saitology9 <[email protected]> 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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)