none
How to compare SQL timestamps in C# ?

    Question

  • Hi,

     

    I want to compare 2 SQL timstamp values in C# and using object.Equals and the two values are actually same but i am always getting the result as false.

     

    How to accomplish ?

     

    Thanks

    Kulvinder Singh

    Wednesday, December 19, 2007 12:06 PM

Answers

  • Binary vs. Varbinary on SQL Server side shouldn't matter to the .Net code -- both map to byte[]/SqlBinary when surfaced in a managed app via SqlClient. As long as the two timestamp values you have are known to come from the same table (and row), you should be able to simply compare the bytes. Stamps from different tables or rows may compare equal without actually representing the same points in time. There is also no guarantee that they'll always compare identically when you think they should, since the stamps on the server may get updated without the rest of the row values changing in some (not common) cases.
    Thursday, April 10, 2008 12:18 AM

All replies

  • Hi,

     

    Try this code :

     

    Code Block

    DateTime d1 = DateTime.Now;

    DateTime d2 = d1;

    if (DateTime.Compare(d1, d2) == 0)

    MessageBox.Show("Equal");

    else

    MessageBox.Show("Not Equal");

     

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

    Wednesday, December 19, 2007 12:28 PM
  • If using SqlDateTime

     

    Code Block

    SqlDateTime d1 = DateTime.Now;

    SqlDateTime d2 = d1;

    if (d1.Equals(d2))

    MessageBox.Show("Equal");

    else

    MessageBox.Show("Not Equal");

     

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

     

    Wednesday, December 19, 2007 12:36 PM
  • Thanks for the reply but as i have already said, the answer is always false even though the byte[] is same

    Wednesday, December 26, 2007 11:00 AM
  • Hi,

     

    What do you mean by byte[] being same? Post your code over here.

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    -----

     

    Wednesday, December 26, 2007 11:34 AM
  •  

    I would guess he means the SQL 2005 timestamp type, not DateTime.

     

    This type is an 8 byte array of sorts...

    I would answer the OP but im struggling too

    Wednesday, April 09, 2008 12:06 PM
  •  PaulPrice wrote:

     

    I would guess he means the SQL 2005 timestamp type, not DateTime.

     

    This type is an 8 byte array of sorts...

    I would answer the OP but im struggling too

     

    That is correct because SQL Server TimeStamp is Binary when NOT NULL and Varbinary when NULL, can it be compared beyond row version I am not sure because though you can get the value it is not DateTime by definition.

    Wednesday, April 09, 2008 1:42 PM
  • Hi,

     

    Ok seems i did not read that post correctly. Sorry about that. In that case, how about doing the following :

     

    Read the timestamp assuming Column1 and Column2 are Sql Timestamp columns. Once we have the array, we can use the SqlDataReader.GetSqlBinary() which get the value of the column as a SqlBinary. Use the CompareTo() method then.

     

    Sample Code:

     

    Code Snippet

    byte[] TimeStmpArray1= dr["@Column1"] as byte[];

    byte[] TimeStmpArray2 = dr["@Column2"] as byte[];

    SqlBinary sql1 = new SqlBinary(TimeStmpArray1);

    SqlBinary sql2 = new SqlBinary(TimeStmpArray2);

    if(sql1.CompareTo(sql2))

    {

    // Your code

    }

     

     

     

    HTH,
    Suprotim Agarwal

     

     

    Wednesday, April 09, 2008 2:36 PM
  • It is not that simple you need the table definition to know if you need binary or varbinary and I am not sure the byte arrays will give you actual comparison.

    Wednesday, April 09, 2008 2:49 PM
  • Hi,

     

    Yes Caddre, quiet right. But what if you add a parameter and can specify the type as:

     

    Parameters.Add(new SqlParameter("@TimeStamp", SqlDbType.Binary));

     

    Also take a look at this thread(a little off-topic) which suggests a practical use of comparing timestamp and how to achieve it through triggers (link is for Sql 2000)

     

    http://sqlserver2000.databases.aspfaq.com/how-do-i-audit-changes-to-sql-server-data.html

     

     


    HTH,
    Suprotim Agarwal


     

    Wednesday, April 09, 2008 3:08 PM
  • Binary vs. Varbinary on SQL Server side shouldn't matter to the .Net code -- both map to byte[]/SqlBinary when surfaced in a managed app via SqlClient. As long as the two timestamp values you have are known to come from the same table (and row), you should be able to simply compare the bytes. Stamps from different tables or rows may compare equal without actually representing the same points in time. There is also no guarantee that they'll always compare identically when you think they should, since the stamps on the server may get updated without the rest of the row values changing in some (not common) cases.
    Thursday, April 10, 2008 12:18 AM