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

Answer 1

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
-----

Answer 2

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
-----

 

Answer 3

Thanks for the reply but as i have already said, the answer is always false even though the byte[] is same

Answer 4

Hi,

 

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

 

HTH,
Suprotim Agarwal

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

 

Answer 5

 

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

Answer 6

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.

Answer 7

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

 

 

Answer 8

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.

Answer 9

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

 

Answer 10

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.