I have used Excel and created 2 columns. column1 contains DATES, column2 contains values as INT. I have imported the table as a CSV using SQLite Manager. I am connected to the DB and have used it with success for other queries that involved text only. Now I would like to SELECT the INT in column2 that corresponds to the current date displayed in column1. Therefore, everyday when the SQL query is ran it will auto-update based on the new current date. I have used
SELECT column2 FROM table WHERE column1 = CURRENT_DATE
However, it returns null (blank, nothing), whereas if I use
SELECT column2 FROM table WHERE column1 > CURRENT_DATE
it returns the INT in column2 of rowid1, but if I use
SELECT column2 FROM table WHERE column1 < CURRENT_DATE
it returns the INT in column 2 of rowid3.
Therefore, the DB is functioning; however, the problem is it cannot associate the current system date with the right value in column1. Is it possible that the import of CSV. into SQLite has altered the datatype to VARCHAR or something other than DATE and INT?
SQLite does not have a DATE data type.
Dates are typically stored as strings (CSV does not have types anyway), and to be compatible with SQLite's built-in date functions, must be in the format yyyy-mm-dd.
Before exporting from Excel, ensure that all dates are formatted with this format.