Compare DateTime with Microsoft SQL and Visual C#

Question

I'm trying to compare a DateTime to a given date.

string conString = @"Provider=Microsoft.JET.OLEDB.4.0;" + @"data 
source=C:\\myDB.mdb";
OleDbConnection conn = new OleDbConnection(conString);
conn.Open();
DataSet ds = new DataSet();
String q = "SELECT * FROM Booking WHERE CheckIn < DATE()";
//String q = "SELECT * FROM Booking WHERE CheckIn < '01.01.2020'";
//String q = "SELECT * FROM Booking WHERE CheckIn < 01.01.2020";
//String q = "SELECT * FROM Booking WHERE CheckIn < '01.01.2020 00:00:00'";
//String q = "SELECT * FROM Booking WHERE CheckIn < 01.01.2020 00:00:00";
OleDbDataAdapter adapter = new OleDbDataAdapter(q, conn);
Console.WriteLine(q);
adapter.Fill(ds);
conn.Close();
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
   MessageBox.Show(dt.Rows[0][0].ToString());
}
else
{
     MessageBox.Show("0 found");
}

The code shows how I compare it to the current time. It works fine and results in

01.12.2018 00:00:00

. But if I try to replace the currentTime to a specific value (see what I've tried), it throws me either System.Data.OleDb.OleDbException or SQL Syntax Error. I'm a bit confused right now because DATE() returns a value in the very same format as I've tried. Does anyone know what I'm missing?

Best regards

Kruspe

Answer 1

Use this format:

yyyy-MM-dd

It's called the ISO-8601 standard date format, and you should always use it when putting in Date literals, in pretty much any language:

2020-01-01

---

While I'm here, the code posted has the potential to leak connection objects in the situation where an exception is thrown. It's better practice to enclose your connection in a using block. As an aside, if you use parameterized queries for this, the formatting suddenly becomes irrelevant.

string dbPath = @"C:\myDB.mdb";
string conString = $"Provider=Microsoft.JET.OLEDB.4.0;data source={dbPath}";
string q = "SELECT * FROM Booking WHERE CheckIn < ?";
Object result = null;

using (var conn = new OleDbConnection(conString))
using (var cmd = new OleDbCommand(q, conn))
{
    cmd.Parameters.Add("Date", OleDbType.Date).Value = new DateTime(2020, 1, 1);
    Console.WriteLine(q);
    result = cmd.ExecuteScalar();
}

if (result == null || result == DBNull.Value)
{
    result = "0 found";
}
MessageBox.Show(result.ToString());