Python与Sqlite3中Varchar类型日期的范围筛选及排序问题求助

Hey Juan, let's dig into why your date filtering and sorting isn't working—this is a common gotcha with SQLite and non-standard date formats!

The Core Problem #

SQLite's date() function only recognizes specific date formats by default, like YYYY-MM-DD (ISO standard) or YYYYMMDD. Your FECHA_RECOR field stores dates as strings in what you noted as dd/mm/yyyy, but your example 07/30/2021 looks like mm/dd/yyyy since 30 can't be a month—we'll adjust for that! Either way, date() can't parse these formats, so when you run date(FECHA_RECOR) it returns NULL. Comparing NULL with your date range will never match any rows, which is why you get no results, and string-based sorting won't behave like proper date sorting.

Quick Fix: Adjust Your SQL Query #

We need to convert the FECHA_RECOR string into a format SQLite understands directly in the query. Using SQLite's string functions, we can split the date parts and rearrange them into YYYY-MM-DD:

If your actual format is mm/dd/yyyy (matching your example 07/30/2021), update your query like this:

query = '''
SELECT * FROM recordatorios 
WHERE date(substr(FECHA_RECOR, 7, 4) || '-' || substr(FECHA_RECOR, 1, 2) || '-' || substr(FECHA_RECOR, 4, 2)) 
BETWEEN ? AND ? 
ORDER BY APELLIDO1_RECOR, date(substr(FECHA_RECOR, 7, 4) || '-' || substr(FECHA_RECOR, 1, 2) || '-' || substr(FECHA_RECOR, 4, 2))
'''

Let's break down the conversion:

• substr(FECHA_RECOR, 7, 4) grabs the year (last 4 characters: 2021 from 07/30/2021)
• substr(FECHA_RECOR, 1, 2) grabs the month (first 2 characters: 07)
• substr(FECHA_RECOR, 4, 2) grabs the day (characters 4-5: 30)
• We concatenate these with - to make 2021-07-30, which date() can parse correctly.

If your format is actually dd/mm/yyyy (swap day and month), adjust the substr positions:

date(substr(FECHA_RECOR, 7, 4) || '-' || substr(FECHA_RECOR, 4, 2) || '-' || substr(FECHA_RECOR, 1, 2))

Your Python code for passing date parameters (fecha11 and fecha22 as date objects) is correct—SQLite will auto-convert them to YYYY-MM-DD strings that match our converted field.

Long-Term Solution: Fix Your Database Schema #

To avoid this headache forever, change the FECHA_RECOR field to a proper DATE type. SQLite doesn't let you alter column types directly, so you'll need to migrate your data:

1. Create a new table with the correct column type:

CREATE TABLE recordatorios_new (
    -- Copy all your existing columns here, replacing FECHA_RECOR's type with DATE
    ID_RECOR INTEGER PRIMARY KEY,
    APELLIDO1_RECOR TEXT,
    -- Add all other columns from your original table...
    FECHA_RECOR DATE
);

2. Migrate your data, converting dates to the correct format:

INSERT INTO recordatorios_new 
SELECT 
    ID_RECOR,
    APELLIDO1_RECOR,
    -- Include all other columns...
    date(substr(FECHA_RECOR, 7, 4) || '-' || substr(FECHA_RECOR, 1, 2) || '-' || substr(FECHA_RECOR, 4, 2))
FROM recordatorios;

3. Replace the old table:

DROP TABLE recordatorios;
ALTER TABLE recordatorios_new RENAME TO recordatorios;

Now you can simplify your query to:

query = 'SELECT * FROM recordatorios WHERE FECHA_RECOR BETWEEN ? AND ? ORDER BY APELLIDO1_RECOR, FECHA_RECOR'

And when inserting new data, use Python's date objects or YYYY-MM-DD strings—SQLite will handle them correctly.

Quick Check #

Double-check your date format first! If your example 07/30/2021 is actually dd/mm/yyyy, that would be invalid (30 isn't a month), so I suspect it's mm/dd/yyyy. Adjust the substr positions if needed, and test with a single row to confirm the conversion works.

内容的提问来源于stack exchange，提问作者Juan carlos Pantoja