In this tip I want to share one of the more interesting aspects of DateTime datatype which I came across while working in the recent past.
We have worked on various aspects and format of DateTime datatype in C# and SQL Server. But here in this section we will discuss about the tick value of Datetime,its properties,its accuracy and how it gets stored in SQL server.
BRIEF:
A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond.
The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001, which represents DateTime.MinValue. It does not include the number of ticks that are attributable to leap seconds.
ANALYZE:
Lets analyze the DateTime.Ticks with an example
// Declaring variable with DateTime DataType
DateTime dtToday = DateTime.Now;
So the value of dtToday will be 7/19/2010 5:51:07 PM
// Getting the tick value of above DateTime Variable dtToday
long dtTick = dtToday.Ticks;
value of dtTick will be = 634151586672641305
Stepping ahead let’s store the same dtToday mentioned above in SQL Server(database) with same datatype (DateTime) and retrieve and manipulate the tick value later. After inserting data to the database and retrieving the same we will find the output to be
// Retrieving the same date time from Database
DateTime dtTodayDB= Convert.ToDateTime(dbCmd.ExecuteScalar());
value of dtTodayDB will be 7/19/2010 5:51:07 PM
// Getting the tick value of Datetime obtained From SQL Server
long dtTickDB = dtTodayDB.Ticks;
value of dtTickDB will be = 634151586670000000
So the value of dtTick is 634151586672641305 which is different from the tick value we get after storing the same in DateTime type. The latter has a value of 634151586670000000.
The reason behind lack of accuracy in SQL Server with that of application is that DateTime data type in SQL Server only has a precision of about .003 seconds (actually values are rounded to .000, .003 or .007) or 10-3 whereas a DateTime data type in .NET has a precision of 100 nanoseconds (10-7) . Accuracy differs here.
EXAMPLE:
For example if we are storing any tick value(long ) somewhere either in database or in cache and at the same time we are storing dateTime value(not the tick) in the database (SQL Server), and if we go on doing comparisons, like getting the tick value as below and converting the same to dateTime
long dtTickCh = 634151586672641305;
DateTime dtTodayCh= new DateTime(dtTickCh);
value of dtToday will be 7/19/2010 5:51:07 PM
and comparing the same to that obtained from database (mentioned in Analyze Section) for example operation such as
if (dtTodayCh == dtTodayDB)
{ // Do Something
}
The above if statement will not be executed ever because of difference in accuracy of storing DateTime datatype in SQL Server and we will not get the expected result.
SOLUTION:
The efficient solution which I can find is that instead of keeping the DateTime data we can have column with long datatype and in place of storing DateTime we can store the tick value of that dateTime variable and while we are retrieving, we can retrieve long tick value converting the same to DateTime datatype and finally we can go for any comparison or manipulation with the same and get the expected output..