【C#】DATE() 関数を使って、日付部分だけを取り出して比較

データベースには日時（たとえば “2025-11-29 10:30:00” みたいな形式）で保存されている。

検索を日にちだけで行う場合。

public string? searchSubject(SQLiteConnection connection, string subject, string? receivedDate)
{
  string query = @"
        SELECT MessageId FROM ReceivedMail
        WHERE Subject = @subject
        AND (
            @receivedDate IS NULL OR DATE(ReceivedAt) = DATE(@receivedDate)
        )
        LIMIT 1;
    ";

    using (var command = new SQLiteCommand(query, connection))
    {
        command.Parameters.AddWithValue("@subject", subject);
        if (string.IsNullOrWhiteSpace(receivedDate))
            command.Parameters.AddWithValue("@receivedDate", DBNull.Value);
        else
            command.Parameters.AddWithValue("@receivedDate", receivedDate);

        using (var reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                return reader["MessageId"].ToString();
            }
        }
    }

    return null