Although SQL Server 2000 has a data type called DateTime, which is very similar to the .NET DateTime data type, it is just that; similar. It is not identical because their precision is different.

Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 1 A.D. (C.E.) in the GregorianCalendar calendar.

MSDN on .NET DateTime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds

MSDN on .SQL Server 2000 DateTime

This means that a .NET DateTime will be different to the original value when saved to and loaded from SQL Server 2000. The way to get round this issue, if it is an issue for your application, is to persist a .NET DateTime’s number of ticks (100 nanosecond units) rather than the DateTime itself. This way instead of using SQL Server 2000’s DateTime, its BigInt data type can be used instead, which maps directly to a .NET’s Int64 (long).

By going this route your application might be happy, but your DBA probably won’t be. He doesn’t care about ticks, he just wants to use SQL’s nice operators that apply to DateTime such as BETWEEN. Thankfully, this can be arranged. SQL Server 2000 allows for tables to have calculated columns, so as long as you know how to convert ticks to DateTime, a calculated column of data type DateTime can be added for every column that stores ticks as BigInt.

The following is a function by Jerry Dixon that converts ticks to DateTime

CREATE FUNCTION dbo.udfTicksToDateTime

(

      @Ticks BIGINT

)

RETURNS DATETIME

AS

BEGIN

      DECLARE @Days  BIGINT

      DECLARE @DaysBefore1753 BIGINT

      DECLARE @TimeTicks BIGINT

      DECLARE @Seconds BIGINT

      SET @Days = @Ticks / CONVERT(BIGINT,864000000000)

      SET @DaysBefore1753 = CONVERT(BIGINT,639905)

      SET @TimeTicks = @Ticks % CONVERT(BIGINT,864000000000)

      SET @Seconds = @TimeTicks / CONVERT(BIGINT,10000000)

      RETURN DATEADD(s,@Seconds,DATEADD(d,@Days - @DaysBefore1753,CONVERT(DATETIME,'1/1/1753')))

END

And you can go the opposite way too if you feel like it.

Calculated columns can be added by doing something like the following:

CREATE TABLE Orders (

        OrderDateAsTicks BIGINT,

        OrderDate AS dbo.udfTicksToDateTime(OrderDateAsTicks)

)