SQLite: compare date
How do you create a query with a date comparison in SQLite?
Solution:
You have to compare the SQL query with a DateTime (date as ISO string)
SELECT * FROM table WHERE [dtField] < Datetime('2018-07-19 00:00:00') |
Example of the query:
Here a SQLite query is created, which shows all records whose date is less than 19.07.2018.
As a result, the data lines appear from line 4
/
? |
Name |
Value |
Type |
sSQL |
"SELECT * FROM tbl_Details WHERE [dtListed] < Datetime('2018-07-19 00:00:00')" |
string |
|
sDate_Listed |
"2018-07-05 07:11:43" |
string |
|
ID |
"4" |
string |
|
sTitle |
"Salesforce Analyst " |
string |
C # Code Example UWP:
Data query in SQLite to date, older than the current day
//----< 1) Set NotUpdated To Delete >---- string sSQL = "SELECT * FROM tbl_Details WHERE [dtListed] < Datetime('" + DateTime.Today.ToString("yyyy-MM-dd 00:00:00") + "')"; SqliteDataReader dataReader = clsDB.Get_DataReader(sSQL); if (dataReader.HasRows == true) { while (dataReader.Read()) { if (optStop.IsChecked == true) return false; //---< NoMatch: Delete on Server >--- string ID = dataReader["IDDetail"].ToString(); string sDate_Listed = dataReader["dtListed"].ToString(); string sTitle = dataReader["Title"].ToString(); fx_Log("ID=" + ID + " dtListed=" + sDate_Listed); //</ nomatch > } }
//----</ 1) Set NotUpdated To Delete >---- return true;
|
In Visual Studio:
Screenshot
SQLReader
public static SqliteDataReader Get_DataReader(string SQL_Text) { //--------< db_Get_DataTable() >-------- SqliteConnection cn_connection = Get_Sqlite_Connection();
//< get Table > SqliteCommand selectCommand = new SqliteCommand(SQL_Text, cn_connection); SqliteDataReader query; try { query = selectCommand.ExecuteReader(); } catch (SqliteException ex) { clsSys.show_Message(ex.Message); return null; } //</ get Table >
//< output > return query; //</ output > //--------</ db_Get_DataTable() >-------- }
|
For this you need a static SQL database connection, SQL string
public static SqliteConnection _sqlite_Connection = null; |
public static SqliteConnection Get_Sqlite_Connection() { //--------< db_Get_Connection() >-------- //< db oeffnen > //SqliteConnection cn_connection = new SqliteConnection(app_settings._sqlite_string); if (_sqlite_Connection == null) _sqlite_Connection = new SqliteConnection(app_settings._sqlite_string); if (_sqlite_Connection.State != ConnectionState.Open) _sqlite_Connection.Open(); //</ db oeffnen >
//< output > return _sqlite_Connection; //</ output > //--------</ db_Get_Connection() >-------- }
|