Sqlite, check the exact difference between a date, time and minutes of the current date

Question

I need to do a check between two dates, I need to check if the date, time and minutes of a field is greater than or equal to the current date, time and minutes, I am using the sqlite.

SELECT * FROM info WHERE strfTime ('%s', dataFinal ) >= strfTime('%s', 'now');

If this condition is true, the data goes to the datagridview_2 otherwise continue in the datagridview_1.

To summarize: The datagridview_2 will only receive information if the current date is greater than or equal to the date of the "dataFinal" column. This date Final, the user who will specify in the field.

Answer 1

The Sqlite does not have a standard type for storing date and time. You can find more information here: https://www.sqlite.org/datatype3.html

However, internal functions allow storing this type of data using TEXT, REAL or INTEGER. For this answer I will use the most common data format to store dates in Sqlite - TEXT.

To check between two dates, you can send a parameter in your query through the method Addwithvalue(). This will avoid Sqlinjection. In the example below, I use the ISO8601 standard which is an international standard for date and time storage. You can find more information about this here:

https://docs.microsoft.com/pt-br/dotnet/standard/base-types/standard-date-and-time-format-strings#the-round-trip-o-format-specifier

Follows the code.

        using (SQLiteConnection connection = new SQLiteConnection(@"sua_conexao_aqui"))
        {
            string consulta = "SELECT * FROM info WHERE dataFinal > @dataFinal";

            DateTime dataFinal = DateTime.Now.AddMonths(-6);

            using (SQLiteCommand commandReader = new SQLiteCommand(consulta, connection))
            {
                commandReader.Parameters.AddWithValue("@dataFinal", dataFinal.ToString("o")); //ISO8601
                SQLiteDataReader reader = commandReader.ExecuteReader();

                while (reader.Read())
                {
                    DateTime suaData = Convert.ToDateTime(reader[0]);
                }
            }
        }