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");

 

                conn.Open();

                int res = cmd.ExecuteNonQuery();

 

                if (res == 1)

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

                else

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

 

                Console.ReadLine();

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s