New Date Data Types in Microsoft SQL Server 2008

By Scott Mitchell

Introduction

In August 2008 Microsoft released the latest version of the database server software, SQL Server 2008. SQL Server 2008 includes a number of new features not found in SQL Server 2005, including: a terser T-SQL syntax; the new MERGE statement; new data types and functions; enhanced encryption and XML support.

In previous versions, SQL Server had only two date-related data types: datetime and smalldatetime, both of which allow date and time values (the difference being that datetime allows for a larger range of possible dates and affords more precision on the time than smalldatetime, but at the cost of larger storage space). SQL Server 2008 introduces four new date data types: time, date, datetime2, and datetimeoffset.

This article explores the time and date data types and shows how they can be used and formatted from within an ASP.NET page. This article also includes a short discussion on the datetime2 and datetimeoffset and compares and constrasts SQL Server 2008's six different date data types. Read on to learn more!

- continued -

An Overview of SQL Server 2008's Date Data Types

Previous versions of SQL Server offered two date data types: datetime and smalldatetime. These data types were sufficient for most applications, but could be cumbersome in certain cases. For instance, both data types have a date and time portion, which is great if that's what you want, but cumberson if all you need to store is just the date or just the time. Similarly, the date ranges imposed by these two data types - 1753-01-01 to 9999-12-31 for datetime and 1900-01-01 to 2079-06-06 for smalldatetime - are insufficient for a small percentage of applications.

SQL Server 2008 remedies these ills by keeping the datetime and smalldatetime and introducing four new date data types: time, date, datetime2, and datetimeoffset. As you can probably guess by their names, the time and date data types track just a time and just a date portion, respectively. The datetime2 data type is like the datetime data type, but has a larger window of dates (from 0001-01-01 to 9999-12-31) and greater percision on the time portion. The datetimeoffset data type has the same range and precision as the datetime2 data type, but enables an offset from UTC to be specified, which makes it easier to record and display times respective to the end user's timezone.

The following table summarizes these six data types format, range, accuracy, and storage size in bytes, and is taken from the Date and Time Data Types and Functions technical documentation.

Data type Format Range Accuracy Storage size (bytes)

time hh:mm:ss[.nnnnnnn] 00:00:00.0000000 through 23:59:59.9999999 100 nanoseconds 3 to 5

date YYYY-MM-DD 0001-01-01 through 9999-12-31 1 day 3

smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 through 2079-06-06 1 minute 4

datetime YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 through 9999-12-31 0.00333 second 8

datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 nanoseconds 6 to 8

datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) 100 nanoseconds 8 to 10

Note: The time, datetime2, and datetimeoffset data types' storage spaces are between a range because when you use the data type you can specify the precision.

The remainder of this article examines the date and time data types in more detail, and shows how to format these data type values when displaying them in an ASP.NET data Web control (such as the GridView). The code and database examined in the remainder of this article is available for download at the end of this article. For more information on the other new date data types (datetime2 and datetimeoffset), consult the Further Readings section at the end of this article.

Creating a Table Using `date` and `time` Data Types

To showcase using the date and time data types I created a SQL Server 2008 database with a table named Employees. This table has the following schema:

Column Data Type Comments

EmployeeID int The primary key; an IDENTITY column

FirstName nvarchar(50)

LastName nvarchar(50)

HireDate date The date the employee was hired.

StartTime time The time of day the employee starts his job.

EndTime time The time of day the employee ends his job.

LastLoginDate datetime The date and time the employee last logged onto the company intranet.

There are four records in the Employees table.

There are four employee records in the database.

Displaying the Employees in an ASP.NET Web Page

After creating this table, I created an ASP.NET web page with a SqlDataSource control that returned all columns and records from the Employees table. I then hooked up this SqlDataSource control to a GridView. (For more information on displaying data in an ASP.NET web page, see my Accessing and Updating Data in ASP.NET article series.)

Without specifying any formatting criteria for the data returned by the SqlDataSource control, the GridView displays the following output.

The data, when displayed in a GridView without specifying any formatting criteria for the data.

Note that the HireDate column (of type date) displays the date along with a time of midnight (i.e., 9/12/2004 12:00 AM). This is because when the HireDate column value is returned from the database it is converted into a DateTime object in the .NET runtime, which has a date and time portion. The reason is because there is no "Date" type in .NET that stores just a date. Also note that the StartTime and EndTime values display as a 24-hour military clock. These values are actually represented in the .NET runtime as TimeSpan objects.

We can format the display of the HireDate column to not include the time portion by setting the BoundField's DataFormatString property to {0:d}. The format specifier "d" formats a date and time value into a "short date value," which is just the date without the time. (For a list of format strings for DateTime objects see the standard DateTime format strings guide and the custom DateTime format strings guide.

Unfortunately, we cannot format the StartTime and EndTime columns in the same way because the TimeSpan object cannot be formatted via the BoundField's DataFormatString property. Instead we must create our own method in code that does the formatting and then convert the BoundField into a TemplateField and call this method, passing in the value returned from the StartTime and EndTime columns.

Start by creating a formatting method in your ASP.NET page's code-behind class (or, better yet, in a separate class in the App_Code folder or in a Class Library). This method should take in an Object (the TimeSpan value coming from the database) and return a string (the output displayed in the page). Here is the formatting method used in the demo:

Protected Function DisplayAs12HourTime(ByVal timeValue As Object) As String If Convert.IsDBNull(timeValue) Then 'Value to return when data is a NULL database value Return "N/A" Else Dim ts As TimeSpan = CType(timeValue, TimeSpan) If ts.Hours >= 12 Then Return String.Format("{0:#0}:{1:00} PM", ts.Hours - 12, ts.Minutes) Else Return String.Format("{0:#0}:{1:00} AM", ts.Hours, ts.Minutes) End If End If End Function

This method takes in an Object and starts by seeing if the Object value is a database NULL value. If it is, the string "N/A" is displayed in the grid cell. If, however, it is not a NULL value then the Object is cast to a TimeSpan instance and its hours and minutes information is displayed in a 12-hour format.

This method needs to be called from the GridView in the .aspx page. To accomplish that, convert the StartTime and EndTime BoundFields into TemplateFields and then configure their ItemTemplates to call the DisplayAs12HourTime method. After doing this, the TemplateFields' declarative markup should look similar to the following:

<asp:TemplateField HeaderText="Start Time" SortExpression="StartTime"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# DisplayAs12HourTime(Eval("StartTime")) %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="End Time" SortExpression="EndTime"> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# DisplayAs12HourTime(Eval("EndTime")) %>'></asp:Label> </ItemTemplate> </asp:TemplateField>

In the above markup the Text property of the Label Web control is assigned the value returned by DisplayAs12HourTime, which is passed in the value of the StartTime and EndTime columns.

With the above changes to the DataFormatString property of the HireDate BoundField and the formatting applied to the StartTime and EndTime TemplateFields, the resulting output now shows just the date for HireDate and a 12-hour clock for the StartTime and EndTime values.

The HireDate, StartTime, and EndTime columns are now formatted.

Comparing `date` and `time` Columns with `datetime` Columns

There may be times when you need to compare the values in a date or time column with those in a datetime column. For instance, you might want to list all employees whose last login time happened during "off hours" (that is, if they logged on at a time outside of their StartTime and EndTime window). The challenge here is that StartTime and EndTime contain just time data, there is no date. Yet the LastLoginDate column is a datetime, which has both a date and time. How do we compare a time column against just the time portion of a datetime? Likewise, how do we compare a date column against just the date portion of a datetime column?

To perform these comparisons we can convert the datetime value into a date or time value, as needed, and make our comparisons from there. For example, to view a list of employees that last logged on during "off hours" we could use the following SELECT query:

SELECT ... FROM [Employees] WHERE ([EndTime] < CAST([LastLoginDate] AS time) OR [StartTime] > CAST([LastLoginDate] AS time))

That's all there is to it! Here I use the CAST(columnName AS dataType) function to do the conversion. You could also use CONVERT(dataType, columnName).

The following screenshot shows the results of the above query in a GridView.

The grid shows those employees who last logged on during off hours.

Conclusion

Microsoft introduced four new date data types in SQL Server 2008: time, date, datetime2, and datetimeoffset. The date and time data types allow for storing just the needed data, whereas the datetime2 data type provides a larger range of possible date values and greater precision than the existing datetime type. And datetimeoffset allows for dates to be stored along with an offset. Be sure to download the demo available at the end of this article. It includes a simple SQL Server 2008 database with a table with columns of types date, time, and datetime, and shows how to format these values in a web page and how to compare date and time values against datetime values.

Happy Programming!

By Scott Mitchell

Attachments:

Download the Demo Code Used in this Article