How to query dates using sqlite between

Question

Im trying to query a range between dates but

i have tried using the date datatype,store the values in the date column as string and also use the date function but not getting the desired results

CREATE TABLE PvcTable (
    date        TEXT NOT NULL,
    Wardname    TEXT NOT NULL,
    Puname      TEXT NOT NULL,
    PvcReceived TEXT,
    PRIMARY KEY (
        date,
        Wardname,
        Puname
    )
);

the expected result is when i query let say

SELECT *  from pvctable 
where date between '2019-1-1' and '2019-12-1' 
order by WARDNAME

i should get all the records between jan - dec 2019, but instead i get this.only 3 records return.

date     Wardname       Puname                    PvcReceived
2019-10  01Alagarno     010KANGARWAPRISCHII         58
2019-11  02Baga         001MILEFOUR                 58
2019-12  02Baga         002DARBASHATA               58

Answer 1

It is important to make sure that the dates in the table have the proper format YYYY-MM-DD which is comparable.
From the sample data you posted I see that there is no DD part in the dates, which is fine if you don't need it, because YYYY-MM is also comparable.
But if there is no DD part then in your query you should not compare the date column with dates containing this part, but with dates in the format YYYY-MM.
So change to this:

SELECT *  from pvctable 
where date between '2019-01' and '2019-12' 
order by WARDNAME

See the demo.
Results:

| date    | Wardname   | Puname              | PvcReceived |
| ------- | ---------- | ------------------- | ----------- |
| 2019-01 | 01Alagarno | 001ALAGARNOPRISCH   | 58          |
| 2019-10 | 01Alagarno | 010KANGARWAPRISCHII | 58          |
| 2019-11 | 02Baga     | 001MILEFOUR         | 58          |
| 2019-12 | 02Baga     | 002DARBASHATA       | 58          |