0

My SQLite table structure is as follows:

Table: Income. 
Columns:   IncomeID, IncomeAmount,  IncomeMonth, IncomeYear

How can i write a SQLite query to retrieve income Amounts between a given date range.

I know, we need to use date function, but i dont know how to specify two columns in it, i mean IncomeMonth and IncomeYear

Arun Chandran Chackachattil
  • 3,356
  • 7
  • 31
  • 45
Satheesh Francis
  • 1
  • 2
  • Take a look at [this question](http://stackoverflow.com/questions/4428795/sqlite-convert-string-to-date) –  Aug 01 '13 at 17:28

1 Answers1

4

If you know the year and month of the given date range, you do not need date functions.

A record is in the range if its year is after the range's start year, or, if both are in the same year, if the record's month is the same or after the range's start month. The comparions for the range end are similar:

SELECT *
FROM Income
WHERE ( IncomeYear > StartYear OR
       (IncomeYear = StartYear AND IncomeMonth >= StartMonth))
  AND ( IncomeYear < EndYear OR
       (IncomeYear = EndYear   AND IncomeMonth <= EndMonth))
CL.
  • 173,858
  • 17
  • 217
  • 259
  • 1
    +1. More importantly, this approach means that the query optimizer can use any suitable indices. It's easy enough to construct date/time values dynamically in the `where` clause, but doing so turns the column references into *expressions* and generally speaking, *expressions* may not be used against indices. That means in this case, if indices existed covering the start/end months, @CL's query could use them to seek to the desired range of rows. Constructing a date/time value on the fly in the where clause would mean that a table or clustered index scan would need to be performed. – Nicholas Carey Aug 01 '13 at 19:33