New date-time data types in SQL Server 2008

SQL Server 2008 finally released the new version, although it was just another community beta rather than a full version. Whenever new software is released, the most problematic question to ask most is: What are some of the new features? SQL Server 2008 has many new features and new tools. However, this article is intended primarily to introduce the new data types introduced in the SQL Server 20,087 month preview, and to specifically discuss the functionality of the datetime data type.

The biggest shift in the DateTime functionality in SQL Server 2008 is the introduction of four datetime data types, DATE, time, DateTimeOffset, and DATETIME2, as well as the addition of new DateTime function features.

The next step is to discuss the functionality and usage of the four new data types.

Date data type

In SQL Server 2005, there is no specific data type that is dedicated only to storing dates (excluding time) and can only be done using datetime or smalldatetime data types. However, when you enter a date, it is shown that there is another time component that needs to be entered, and its initial display is 12:00 AM. If you want to display only the part of the date in the output, you must modify the output format. In most cases, you can use the GETDATE () function to store the current date. Saving the function value of getdate () in a smalldatetime or datetime column in SQL Server 2005 also saves the current time, which can cause many problems. For example, if you use the following statement, you want to search for a record based on a given date:

SELECT * from Tbldate Where [Date] = ' 2007-12-01 '

Since the date column contains a time component, the command will not execute properly, so you must perform the following query:

SELECT * from Tbldate Where DateDiff (d,[date], ' 2007-12-01 ') =0

Although the above query can work, the index that matches the requirements in the date column is likely to not be used. However, you can still use the above query to pull out a small number of records. Although you can take advantage of workspaces, it is clear that you need to add a date data type that removes the time value to reduce potential errors. Take a look at the following syntax:

DECLARE @dt as DATE
SET @dt = getdate()
PRINT @dt

The output of the above script is only dated and does not include the time part. The date data type is scoped from 0001-01-01 to 9999-12-31.

Time Data type

Just like the date data type, you can use the time data type if you only want to store the temporal data without needing a date part. Here is an example of querying using the time data type:

DECLARE @dt as TIME
SET @dt = getdate()
PRINT @dt

The above script output contains only the time section, which ranges from 00:00:00.0000000 to 23:59:59.9999999.