Why my query is returning wrong dataset in SQLITE?

Question

I have a column by the name 'Date' these dates are in format m/d/yyyy, when i query "Select Date from table_nm where Date between '1/2/2015' and '1/9/2015' i get wrong results.

"1/2/2015"
"1/5/2015"
"1/6/2015"
"1/7/2015"
"1/8/2015"
"1/9/2015"
"1/20/2015"
"1/21/2015"
"1/22/2015"
"1/23/2015"
"1/26/2015"
"1/27/2015"
"1/28/2015"
"1/29/2015"
"1/30/2015"

Answer 1

Your issue is that the dates are not in a format that SQLite accepts.

Valid formats can be found here SQL As Understood By SQLite - Date And Time Functions - Time Strings

The format used for your dates is particularity awkward as both months and days can be either 1 or 2 characters. The most likely suitable format would be YYYY-MM-DD e.g. 2015-01-30.

You could use the following as a basis of a solution :-

SELECT  *,
    CASE 
        WHEN CAST(month AS INTEGER) < 10 AND CAST(dayinmonth AS INTEGER) < 10 THEN year||'-0'||month||'-0'||dayinmonth
        WHEN CAST(month AS INTEGER) < 10 AND CAST(dayinmonth AS INTEGER) >= 10 THEN year||'-0'||month||'-'||dayinmonth
        WHEN CAST(month AS INTEGER) >= 10 AND CAST(dayinmonth AS INTEGER) < 10 THEN year||'-'||month||'-0'||dayinmonth
        WHEN CAST(month AS INTEGER) >= 10 AND CAST(dayinmonth AS INTEGER) >= 10 THEN year||'-'||month||'-'||dayinmonth
    END AS newdate
FROM (
    SELECT 
        *,
        substr(date,length(date)-3) AS year, 
        substr(date,1,instr(date,'/')-1) AS month,
        substr(
            substr(date, instr(date,'/')+1),
            1,
            instr(substr(date, instr(date,'/')+1),'/') -1
            )
            AS dayinmonth
    FROM mytable002 
) 
WHERE newdate BETWEEN '2015-01-02' AND '2015-01-09'

Testing Results :-

The table mytable002 used for testing is as per :-

• It actually has more rows
• date2 and date3 were added to help demonstrate the inclusion of other columns.

The resultant output is :-

Notes

• mytable002 would have to be changed accordingly.
• the resultant data includes additional columns (original are underlined blue new columns are highlighted)
  • As * is used all original columns will be available in the resultant data

Extra re comment :-

Thanks , i have imported this data using python into sqlite, converting each and every row would not be easy.

The above could be basis for a very easy conversion. You could add a column and then store the re-formatted date, based upon the above, into that column.

e.g. :-

ALTER TABLE mytable002 ADD COLUMN newcolumn TEXT;
WITH updatedata (newdate,olddate) AS (
    SELECT 
        CASE 
            WHEN CAST(month AS INTEGER) < 10 AND CAST(dayinmonth AS INTEGER) < 10 THEN year||'-0'||month||'-0'||dayinmonth
            WHEN CAST(month AS INTEGER) < 10 AND CAST(dayinmonth AS INTEGER) >= 10 THEN year||'-0'||month||'-'||dayinmonth
            WHEN CAST(month AS INTEGER) >= 10 AND CAST(dayinmonth AS INTEGER) < 10 THEN year||'-'||month||'-0'||dayinmonth
            WHEN CAST(month AS INTEGER) >= 10 AND CAST(dayinmonth AS INTEGER) >= 10 THEN year||'-'||month||'-'||dayinmonth
        END AS newdate,
        date AS olddate
    FROM (
        SELECT 
            *,
            substr(date,length(date)-3) AS year, 
            substr(date,1,instr(date,'/')-1) AS month,
            substr(
                substr(date, instr(date,'/')+1),
                1,
                instr(substr(date, instr(date,'/')+1),'/') -1
                )
                AS dayinmonth
        FROM mytable002 
        ) 
    )
UPDATE mytable002 
SET newcolumn = (
    SELECT newdate
    FROM updatedata
    WHERE mytable002.date = olddate
    )
;