SQLite WHERE with date field

  1. 6 months ago

    Bill

    14 Dec 2018 Pre-Release Testers, Xojo Pro Longview Texas

    I need to select records dated on or after a specified date. The SQL statement I wrote is: "SELECT * FROM CashReceipts WHERE Date >= " + dStartDate.SQLDate + " ORDER By Date"

    Date in the database is defined as "DATE" and was stored as date.sqldate.

    But I end up with every record in the database. How should this select be structured?

    solution is simple... put ticks around the string

     "SELECT * FROM CashReceipts WHERE Date >= '" + dStartDate.SQLDate + "' ORDER By Date"

    other wise you get
    "SELECT * FROM CashReceipts WHERE Date >= 2018-12-14 ORDER By Date"
    which resolves down to
    "SELECT * FROM CashReceipts WHERE Date >= 1992 ORDER By Date"
    as it will take 2018 minus 12 minus 14

  2. Jean-Yves P

    14 Dec 2018 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    did you get any error code or message after the sqlselect ?

  3. Bill

    14 Dec 2018 Pre-Release Testers, Xojo Pro Longview Texas

    no error code. code executed but the results were like the where clause wasn't there.

  4. Jason P

    14 Dec 2018 Xojo Inc Texas

    @BillPlunkett I need to select records dated on or after a specified date. The SQL statement I wrote is: "SELECT * FROM CashReceipts WHERE Date >= " + dStartDate.SQLDate + " ORDER By Date"

    Date in the database is defined as "DATE" and was stored as date.sqldate.

    But I end up with every record in the database. How should this select be structured?

    This probably does the comparison as a string. Maybe something like the following to make sure the comparison is done as DATES not strings:

    "SELECT * FROM CashReceipts WHERE Date(Date) >= Date(" + dStartDate.SQLDate) + ") ORDER By Date"

  5. Dave S

    14 Dec 2018 Answer San Diego, California USA

    solution is simple... put ticks around the string

     "SELECT * FROM CashReceipts WHERE Date >= '" + dStartDate.SQLDate + "' ORDER By Date"

    other wise you get
    "SELECT * FROM CashReceipts WHERE Date >= 2018-12-14 ORDER By Date"
    which resolves down to
    "SELECT * FROM CashReceipts WHERE Date >= 1992 ORDER By Date"
    as it will take 2018 minus 12 minus 14

  6. Bill

    14 Dec 2018 Pre-Release Testers, Xojo Pro Longview Texas

    Thanks Dave, that fixed it....

or Sign Up to reply!