SQL Server 2008 New Feature: new Date/Time datatype

SQL Server 2008 comes with many new features like Merge statement (similar to UPSERT which can perform both Insert and Update based on a condition), Table value parameters (allows to pass arrays to a Stored Proc). Apart from these SQL Server 2008 introduces new date/time datatypes. In this blog post I would cover new Date/Time datatype. Watch my future blogs for other new features.

Till now (up to SQL Server 2005) the Datetime datatype could stores dates ranging from 1753 to 9999. It also stores the time. There are two problems with old Datetime datatype –

  • Using the old Datetime datatype it’s not possible to store dates before 1753 which is the min value supported by old Datetime datatype.
  • The old Datetime datatype also stores time apart from date info. So in case where time value is not required, some space is still wasted for time part.

To solve these problems SQL Server 2008 introduces four new date/time related datatypes –

DateTime2: The new DateTime2 datatype can store date ranges from 0001 to 9999 years. It’s not limited with min value of 1753. Also it allows specifying precision which can save space –

[JoinDateTimeNew] [datetime2](7)

The precision of Datetime2 datatype is better than old Datetime datatype. Datetime2 has a precision of 100 nanoseconds. It has Variable Precision (0 to 7) for decimal places for seconds.

Date: This a new datatype introduced in SQL Server 2008 which allows to store date only, not time. This should be used instead of Datetime or Datetime2 when storing time is not required. Date can store daterange from 01-01-0001 to 31-12-9999.

[BirthDate] [date]

Time: This new datatype introduced in SQL Server 2008 which allows to store time only. It has Variable Precision (0 to 7) for decimal places for seconds.

[BirthTime] [time](7)

Following is an example of create table script with new datatypes –

CREATE TABLE [dbo].[DateTimeTest](

       [Id] [int] NOT NULL,

       [BirthDate] [date] NULL,

       [BirthTime] [time](7) NULL,

       [JoinDateTime] [datetime] NULL,

       [JoinDateTimeNew] [datetime2](7) NULL


Following are the data entered into the table –

.NET Framework SP1 updates .NET Framework with changes that allows to use these new set of datatypes. For example the SqlDBType enum is updated for the new datatypes –

Following code tries to insert one row into the DateTimeTest table using ADO.NET –

DateTimeTest schema:


.NET Code:


SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();

            connBuilder.DataSource = @".\sqlexpress08";

            connBuilder.InitialCatalog = "RedRockDB";

            connBuilder.IntegratedSecurity = true;

            string sql = @"INSERT INTO DateTimeTest VALUES (@Id, @BirthDate, @BirthTime, @JoinDateTime, @JoinDateTimeNew)";


            using (SqlConnection conn = new SqlConnection(connBuilder.ConnectionString))

            using (SqlCommand cmd = new SqlCommand(sql, conn))


                cmd.Parameters.Add("@Id", SqlDbType.Int);

                cmd.Parameters[0].Value = 1;


                cmd.Parameters.Add("@BirthDate", SqlDbType.Date);

                cmd.Parameters[1].Value = DateTime.Parse("1/1/0001");


                cmd.Parameters.Add("@BirthTime", SqlDbType.Time);

                cmd.Parameters[2].Value = new TimeSpan(2, 30, 45);


                cmd.Parameters.Add("@JoinDateTime", SqlDbType.DateTime);

                cmd.Parameters[3].Value = DateTime.Now;


                cmd.Parameters.Add("@JoinDateTimeNew", SqlDbType.DateTime2);

                cmd.Parameters[4].Value = DateTime.Parse("01/01/0001 07:30 AM");



                int res = cmd.ExecuteNonQuery();


                if (res == 1)

                    Console.WriteLine("1 row inserted into DB");


                    Console.WriteLine("Error executing SQL statement");




C# Default Binder

In an earlier post while discussing about the new dynamic keyword in C# 4, I mentioned about DLR (Dynamic Language Runtime). DLR is part of .NET Framework 4 and allows late dynamic binding for dynamic languages like IronPython and IronRuby. Under the hood C# also uses DLR for resolving methods/properties while using dynamic in C#.

In the same blog post I mentioned that normally for all other scenarios, C# compiler uses the default static Binder, which binds members statically during compilation. The Default Binder is exposed through Type.DefaultBinder property, as part of .NET Reflection API. Following example shows how to use it from C# code.

I declared a public class called Calculator which has a method called Add with three overloads –

public class Calculator


        public int Add(int val1, int val2) { return val1 + val2; }

        public double Add(double val1, double val2) { return val1 + val2; }

        public float Add(float val1, float val2) { return val1 + val2; }


I tried to use the Default Binder to binding a method call to particular overload –

     // Get a reference of the Default Binder

            Binder defaulttBinder = Type.DefaultBinder;

            // Create a MethodInfo array of all public methods of Calculator class

            MethodInfo[] calculatorMetods = Assembly.GetExecutingAssembly().GetExportedTypes()[0].GetMethods();           

            // declare an object array of parameter values

            object[] parameters = {50, 100};

            // declare an object array of parameter names

            string[] parameterNames = {"val1","val2"};

            object state; 

            // Call BindToMethod of Default Binder passing MethodInfo array of all public methods of Calculator

            // class, parameter values, parameter names. Parameter Modifiers and culture info is passed as null

            // BindToMethod returns the selectd method from the MethodInfo array passed as input parameter

            MethodBase selectedMethod = defaulttBinder.BindToMethod(BindingFlags.Instance, calculatorMetods,

                ref parameters, null, null, parameterNames, out state);

            // Display the parameter name and type of the returned method

            foreach (var parameter in selectedMethod.GetParameters())


                Console.WriteLine("Parameter Name: {0}, Type: {1}", parameter.Name, parameter.ParameterType);



 Here’s the output. As expected the Binder binds it to the Add overload taking two integers and returning integer.


 Reflection APIs use the Default Binder internally.

Knowing the installed versions of CLR

If you want to know about the different versions of CLR installed on your machine, use the CLRVer command line tool. It shows all the installed versions of CLR as shown below –


As you can see my laptop has the CLR Version 4.0 installed.

If you want to know about the different processes running on your machine, that’s using CLR use the –all switch. It shows the process id, name and the CLR version that it used by the process.