Comparing C# datetime value to SQL Server Compact 4 datetime

Question

I'm building an application that stores a group of datetimes to keep track of when I print a particular report. The report is built up of information from a second table that also has datetimes. I'm trying to get the report to populate a datagridview with records that are only after the last datetime in the first table.

The first table is called 'deliverylog', this table stores the past print dates. The second table is called 'joblog', and it stores the records of previous job entries.

When I run the program, it works just fine and populates the gridview with all records after the last date, but it's not refined... it only populates with dates after the date and not the time. I need the query to populate the gridview to the second....

DateTime lastDeliveryDate;

private void getLastDelivery() // Sets global variable lastDeliveryDate to the last timestamp entered in the deliverylog table 
{
    openLogConnection();

    try
    {
        command = new SqlCeCommand("SELECT TOP 1 * FROM deliverylog ORDER BY Id DESC", logConn);

        drLogSet = command.ExecuteReader();
        while (drLogSet.Read())
        {
            lastDeliveryDate = Convert.ToDateTime(drLogSet["Timestamp"]);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        logConn.Close();
    }
}

private void populateGridView()
{
    openLogConnection();

    try
    {
        command = new SqlCeCommand("SELECT * FROM joblog WHERE TimeStamp > @date", logConn);
        command.Parameters.AddWithValue("@date", lastDeliveryDate);

        dtLogSet = new DataTable();
        bsLogSet = new BindingSource();
        daLogSet = new SqlCeDataAdapter(command);
        cbLogSet = new SqlCeCommandBuilder(daLogSet);

        daLogSet.Fill(dtLogSet);
        bsLogSet.DataSource = dtLogSet;
        dataGridView1.DataSource = bsLogSet;

        dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        logConn.Close();
    }
}

Anyone know how to get the this working right? I'm storing the timestamps for both tables as datetime data types and in the following format: "MM/dd/yyyy hh:mm:ss tt"

Unless your `TimeStamp` field is a `varchar`, you're not actually storing timestamps in *any* string format, and that is good. Can you verify the type of your `TimeStamp` field? — Matt Johnson-Pint, Jul 01 '13 at 15:21

score 0 · Answer 1 · answered Jul 01 '13 at 04:49

0

I believe that using the AddWithValue method is internally converting the value and probably loosing the desired time precision. Instead, use the Add(String, SqlDbType) method overload of the Parameters collection:

var dateParameter = command.Parameters.Add("@date", SqlDbType.DateTime);
dateParameter.Value = this.lastDeliveryDate;

Make sure you have the correct value before setting the parameter value by inspecting the variable using the debugger.

And you could try using the DATEDIFF SQL function instead of the > operator to ensure proper precision:

SELECT * FROM joblog WHERE DATEDIFF(second, @date, TimeStamp) > 0

answered Jul 01 '13 at 04:49

Xint0

5,221
2
27
29

Actually the add() method is deprecated... I got it to work though, it was the way I was storing the datetime value in the first place, I was using standard time and trying to read it as military time... lol Figures it would be a stupid mistake like that.... – Josh C. Jul 01 '13 at 11:51
The `Add(String, Object)` method overload is the one that has been deprecated and replaced with `AddWithValue(String, Object)`. The `Add(String, SqlDbType)` method overload is not deprecated. Don't feel bad data format disparity is quite common a mistake, that's why I recommended inspecting the parameter value while debugging. – Xint0 Jul 02 '13 at 02:54

score 0 · Answer 2 · answered Jul 01 '13 at 15:27

The problems is probably this line:

lastDeliveryDate = Convert.ToDateTime(drLogSet["Timestamp"]);

Assuming your TimeStamp field is a datetime type, you should use:

lastDeliveryDate = (DateTime) drLogSet["TimeStamp"];

Please confirm the data type of your TimeStamp field.

score -1 · Answer 3 · edited May 23 '17 at 11:56

-1

You should format the date to yyyyMMdd hh:mm:ss.

lastDeliveryDate.toString("yyyyMMdd hh:mm:ss").

In this postare more details. How to compare sqlite TIMESTAMP values

edited May 23 '17 at 11:56

Community

1
1

answered Jul 01 '13 at 01:24

jhonatas

1

He's not running SQLite, and string formats are for display and parsing, not for comparison. – Matt Johnson-Pint Jul 01 '13 at 15:23

Comparing C# datetime value to SQL Server Compact 4 datetime

3 Answers3