System.Formatexception: 'String not recognized as valid Datetime

Question

Hello, I am trying to update data from a row of a db sqlite, however I am having problem in a date field.

I enter data in the Registered field with the following code, where Tb_data_registered is a Datetimepicker:

command.Parameters.Add("@Registrado", DbType.Date).Value = TB_Data_Cadastro.Value.Date;

After entering and consulting the database is saved this way: 2020-12-03 00:00:00

However, when changing any row with the following command the date type changes:

SQLiteCommand command = new SQLiteCommand("UPDATE Produtos_TB SET Registrado = '" + TB_Data_Cadastro.Value.Date + "' WHERE ID = '" + id + "'");

And consulting the database in the same field become: 3.12.20 00:00:00

So from when the date is in this second format (3.12.20) any query or something you do in db returns me this error:

System.FormatException: 'Cadeia de caracteres não foi reconhecida como DateTime válido.'

I did a search and found that Sqlite uses a date type that is yyyy-MM-dd HH:mm:ss, based on that I tried to format the date for the line to be updated this way, but without success. When registering I saw that maybe a conversion of System.Datetime to Dbtype.Date, but I could not apply something similar in my code.

Answer 1

Sqlite does not have a specific type for date storage like Sqlserver, by default they are stored with the type Text in ISO8601 string format. Then you need to format your Date in that format.

See the example below:

using (var command = new SqliteCommand())
{
    var dataFormatada = TB_Data_Cadastro.Value.Date
                            .ToString("s", System.Globalization.CultureInfo.InvariantCulture);
    
    command.Parameters.Add("@Registrado", SqliteType.Text).Value = dataFormatada;                
    command.Parameters.Add("@Id", SqliteType.Integer).Value = 1;

    //command.Connection = {*SUA CONEXAO*}
    command.CommandType = CommandType.Text;                
    command.CommandText = 
         @"UPDATE Produtos_TB 
           SET Registrado = @Registrado
           WHERE Id = @Id";

    _ = command.ExecuteNonQuery();

}