Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

SQL String in SQLite with Date-Compare

19.07.2018 (👁9448)

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 == truereturn 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() >--------

}