Get data between two dates based on month and year in SqlLite-CodePudding

I have a table set with a few columns of which my date of joining is a Date type column, I want to find all the employees who joined the organization between Jan 1980 to June 1985 [this could change based on incoming inputs],

If it was the entire date part we can directly put the dates in BETWEEN but not sure what to do in case of only month and year , using strftime will return string values that won't be idle for putting in a BETWEEN

Expected query

select * from table where dateA BETWEEN 'YYYY-MM' AND 'YYYY-MM'

What I have tried till

select * from table where (strftime('%Y', dateA) BETWEEN '1980' AND '1985') AND (strftime('%m', dateA) BETWEEN '01' AND '06')

But it is not returning the expected output

Note: I be getting only Month and Year as input from the APIs

CodePudding user response：

If the column's dateA format is YYYY-MM-DD and the dates that you get are in the format YYYY-MM then for the starting date you must concatenate -01 and for the ending date use the function date() with modifiers that calculate the last day of the month:

SELECT * 
FROM tablename 
WHERE dateA BETWEEN 
            ?1 || '-01' 
            AND 
            date(?2 || '-01', ' 1 month', '-1 day');

Replace ?1 and ?2 with your starting and ending dates in the format YYYY-MM.

See the demo.

CodePudding user response：

You should specify the full dates, including the day component. Assuming your dates be stored in YYYY-MM-DD format:

SELECT *
FROM yourTable
WHERE dateA >= '1980-01-01' AND dateA < '1985-07-01';