CODEWITHSUNDEEP
c#sql-serverdatetime
Ksice
Ksice

Reputation: 3327

How to compare sql datetime and c# datetime

This was born from my previous question

I have a DateTime in c#.

Then this value is inserted to database.

After that select this value and compare that date is the same as it was in the beginning.

What is the best way to do this? Since SQL datetime has different ticks, DateTime from the first step will not be the same as SQL DateTime (row["MyDate"])

How to compare them?

Upvotes: 3

Views: 12715

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460028

You can use the SqlDateTime structure.

DateTime now = DateTime.Now;
SqlDateTime sqlNow = new SqlDateTime(now);
bool equal = now == sqlNow.Value; // false

So if you have a DateTime and want to know if it's equal to a DB-DateTime use:

Assert.Equal(dbEndTime, new SqlDateTime(endTime).Value); //  true

SqlDateTime:

Represents the date and time data ranging in value from January 1, 1753 to December 31, 9999 to an accuracy of 3.33 milliseconds to be stored in or retrieved from a database. The SqlDateTime structure has a different underlying data structure from its corresponding .NET Framework type, DateTime, which can represent any time between 12:00:00 AM 1/1/0001 and 11:59:59 PM 12/31/9999, to the accuracy of 100 nanoseconds. SqlDateTime actually stores the relative difference to 00:00:00 AM 1/1/1900. Therefore, a conversion from "00:00:00 AM 1/1/1900" to an integer will return 0.

Upvotes: 5

PaulF
PaulF

Reputation: 6773

Subtract one from the other & check the ticks of the resulting TimeSpan to be within acceptable limits for the difference in tick length

Upvotes: 5

sangram parmar
sangram parmar

Reputation: 8726

if you ignore millisecond difference than you can try this

Select * from MyTable DATEADD(ms, -DATEPART(ms, endTime), endTime) = @value

Upvotes: 0

Related Questions