SQLite Date Comparison

Databases
1

A small table I have contains just 3 fields; PayPKID (Primary Key Integer), PayStartDate (SQLDate), PayEndDate (SQLDate).

I want to take a correctly formatted date value, validated by using ParseDate(), then find which, if any, record where the supplied date falls between the PayStartDate and PayEndDate.

There is an SQLite ‘BETWEEN’ that should satify this; the example I found was this :

SELECT * FROM tablename WHERE date BETWEEN “11/1/2011” AND “11/8/2011”

The modified version of the above statement would swap the 2 dates like so :

SELECT * FROM tablename WHERE date BETWEEN PayStartDate AND PayEndDate

Since we can’t put our values directly in the SQL string above, am I right in saying I need to use a preparedstatement and pass the date I am interested in as the parameter ? Or does anyone know of an easier/more efficient way to do this check ?

I have not coded anything yet; the way Im thinking to achieve this is to create a method passing the inputted date as a parameter and returning an integer value which represents the PayPKID if a match is found or -1 otherwise.

All dates are stored in SQLDate format and the input date would also be in SQLDate format as a result of the ParseDate, for the purpose of the comparison.

Am I on the right lines here ?

Thanks all.

Edit : The other consideration is that we dont have a bindtype for date values, so Im assuming it has to be converted to a string before passing ?

2

A quick bit of code that seems to work :

Dim DateIsValid As Boolean
Dim NativeDate As New Date
  
  DateIsValid = ParseDate(MySuppliedDate, NativeDate)
  
  If Not DateIsValid Then
    MsgBox("Unable to parse date")
  Else
    Dim MyDB As New SQLiteDatabase
    MyDB.DatabaseFile = GetFolderItem("MyDBFile.sqlite")
    
    If MyDB.Connect Then
      Dim PayPeriodItem As RecordSet
      Dim PayPeriodList As SQLitePreparedStatement = MyDB.Prepare("SELECT * FROM PayPeriods WHERE ? BETWEEN PayStartDate AND PayEndDate LIMIT 1")
      PayPeriodList.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
      PayPeriodItem = PayPeriodList.SQLSelect(NativeDate.SQLDate)
      
      If MyDB.Error Then
        MsgBox("Database error :" + Chr(13) + Chr(13) + MyDB.ErrorMessage)
      Else
        If PayPeriodItem.RecordCount = 1 Then
          Return PayPeriodItem.IdxField(1).IntegerValue
        Else
          Return -1
        End If
      End If
      MyDB.Close
    Else
      MsgBox("Unable to connect to database")
    End If
  End If
3

Yup, you figured out that you need to use the SQLDate format.

4

Yep, the ParseDate() really helps with this too since I set the return value to theDate.SQLDate, which is perfect for storing and doing the comparisons.

Does the BETWEEN check above look reasonable ?

I’ve used LIMIT 1 in the query since the records in the table are already checked to make sure there are no overlaps in the date ranges. The way of thinking is that I get a -1 returned if the date does not already exist within a date range in the table, otherwise I get the PKID of the range where it does exist.

5

Yeah, it looks reasonable. If you’re not sure, create some dummy data and do some testing.

6

You may want your sql to look like

SELECT * FROM PayPeriods WHERE date(?) BETWEEN date(PayStartDate) AND date(PayEndDate) LIMIT 1

JUST to make sure sqlite considers these things as dates

7

[quote=170718:@Norman Palardy]You may want your sql to look like

SELECT * FROM PayPeriods WHERE date(?) BETWEEN date(PayStartDate) AND date(PayEndDate) LIMIT 1

JUST to make sure sqlite considers these things as dates[/quote]

Interesting approach; I’ll definitely do that though. The columns in the file are defined as type Date but this will be a good precaution.

Thanks Norman.

8

You can also use the datetime function to change the time zone, which can be very handy.

9

Sure can
My point was FORCE sqlite to treat these as dates (or timestamps or whatever) so comparison are “date” not “string”