[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
New Data Types

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

SQL Server 2008 - New Data Types


Applies to: SQL Server 2008 Community Technology Preview November 2007.
 

SQL Server 2008 introduces new data types. Here you will find some of them:
 

 Data Type Description
DATE Only a date is stored when this data type is used. Long awaited for many programmers.
TIME A new data type to store only the time, also long awaited for programmers.
DATETIMEOFFSET A time zone aware datetime type
DATETIME2 This data type offers a larger fractional seconds and year range than existing DATETIME data type.

The following images illustrate how the data is represented when using some of these data type.

 

 

 



Benefits.

  1. Some of the new datetime data types require a smaller amount of memory than older datetime data types. Date data type requires just 3 bytes. Time data type requires 7 bytes, one byte smaller than datetime.
  1. Commonly used datetime functions like GETDATE, DATEADD, DATEDIFF, DAY, MONTH, YEAR will work seamlessly with the new datetime data types, taking place implicit data conversions when needed. See the sample script below.

 

 

  1. Separate date and time mean less programming code. No need to use the Convert function to separate date and time anymore.
  2. DATETIME2 offers user defined precision for time values and a larger data range.
  3. Datetime cannot store dates earlier than January 1, 1753. The new "date" data type allows to store dates earlier than year 1753(1).
     

Considerations

There are some differences you should know when comparing data stored in different datetime data types.

For example, two variables storing the same date, one of the variables has a Datetime data type and the other one has a Date data type. When compared they are the same only if the time part is not specified (or filled up with zeros) at the moment the datetime data type variable is inputted/updated.  Please see the images below for illustration.

 

 

 

Also, see what happens when you use the operand plus with a variable using the new Date type. 

 

 

In this case use the DATEADD Function mentioned above.

Take in consideration also, that the SET DATEFORMAT ydm is not supported for date, datetime2, and datetimeoffset data types. Please see BOL for more information.


References.

(1) Demo Script. Date and Time Support in SQL Server 2008.
Found on SQL Server 2008 R2 Update for Developers Training Kit (June 2010 Update).


 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.