Date and Time Data Types in SQL Server 2008

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
08:54

Hello and welcome to another SQL Server Video. My name is Michael Campbell, and in this video we're going to be looking at some of the great new advances in SQL Server 2008 for managing date and time data types.

And to do that, we'll take a look at these new date time data types themselves, and we'll also take a brief look at some of the new intrinsic T-SQL functions that were created for use with these new data types.

To get started, let's look at what might appear to be a strangely-named new datatype, the datetime2 datatype.

And the reason I say that this datatype seems to be a bit strangely named becomes obvious when you look at a few other existing data types out there that allow different variants with different storage sizes and options.

For example, take a look at how tiny ints, small ints, ints, and big ints work - they provide different storage options and capabilities.

And the same thing goes for smalldatetimes and normal datetimes. To the point where it might seem to make more sense to call a new datetime datatype - that stores more temporal data than a smalldatetime and a datetime - a longdatetime.

The problem, however, of thinking of a datetime2 as only being a 'bigdatetime' is that this new datatype does a lot more than provide bigger storage capabilities.

Granted, it does allow for coverage of a larger span of time than its 'smaller' counterparts, but it also provides a whole new approach, or way of dealing with the notion of temporal precision - which means that it really is a new kind of datatype altogether.

And that, in turn, means that calling it a datetime2 actually makes a lot of sense - because it's just not the same as an old run-of-the-mill datetime.

And to get a feeling for just how different this approach is, let's take a peek at Books Online, which shows just how much more precise, and expansive, this data type is in terms of storing dates than its predecessor.

Likewise, as you can see, storage sizes for this data type depend upon how precise you need it to be.

And this new approach to treating optional precision is a common trait shared by many of the newer date time datatypes introduced by SQL Server 2008.

So, for example, there's a great new datetimeoffset datatype - which is effectively just a datetime2 with two additional bytes thrown in to track deviation from UTC time - making this datatype very handy for increasingly complex applications that need to store timestamps in time-zone aware fashion.

And this new approach to precision also makes its way into the new time datatype - which is one of my favorite additions to SQL Server 2008 - along with it's equally specific counterpart, the date data type.

Of course, the obvious benefit of these datatypes is that in many applications you'll frequently run into cases where you don't need to track both a date and a time.

For example, an application that keeps track of employee hire dates doesn't REALLY need to know the exact hour, minute, and second that an employee was hired - they just want to know the date. Likewise, an application that keeps track of when customers prefer to be called during the day only needs to keep track of times - not dates.

So, to get around problems like these in the past, developers and DBAs have typically either taken the approach of creating their own representations of temporal data - either by using char or varchar datatypes, or by expressing dates as integers or decimals, and so on.

Or, a more common approach is to just use a convention where the part of the datetime datatype that isn't needed is ignored. So, for example, when tracking birthdates, it's common to see applications that set the time portion of a birth-date entry to midnight. Likewise, it's pretty common to see dates set to January 1st, 1900 for datetimes that are only tracking the time portion.

The problem, of course, with these approaches is that they're really a form of using 'magic numbers' - as certain values are assigned 'magical' properties that are really only known by convention - and that convention then has to be translated into all of the code that interacts with this data.

On the other hand, if you take the approach of storing times or dates using your own, custom, datatypes, then you'll typically run into problems with defining calculations, and you can even bump into issues with sorting, scanning, aggregating, and even reporting on that data.

Which is one reason why these new, distinct, data types are so nice.
Likewise, with some of the new T-SQL enhancements introduced in SQL Server 2008, I also personally love how much easier it is to define SARGable arguments for common date-time scenarios - as it's now much easier to establish start and end boundaries to temporal queries by merely using a date datatype which can easily be compared against datetime datatypes in cases where you want to return bounded results.

Now, in terms of working with these new data types, there are three main things to consider:

First, how to populate values for these new datatypes when working with T-SQL.
Second, how to CAST and CONVERT them from one datetype to another as needed, and

Finally, how to format these values when using them in query output or reports.
In terms of populating values, you're still able to populate all of these new datatypes manually, or statically, so these examples using hard-coded text will all work just fine - and end up being translated into the necessary underlying data types in terms of storage.

In cases where you need to dynamically populate these data types, especially for the new, higher-precision, variants, SQL Server 2008 introduces some new T-SQL intrinsic functions that you can use to populate these values dynamically - as you can see here.

And, of course, these new functions are fully documented within Books Online. To find them, just check out the functions listing in the T-SQL Language documentation section of Books Online.

And while we're here, let's go ahead and take a look at the DATEADD function - which has been around ... basically ... forever.

But note, that with SQL Server 2008, it now has options to account for Microseconds and Nanoseconds.
The same goes for the DATEDIFF function - meaning that with both of these functions you're now able to fully take advantage of the new precision offered by the new SQL Server 2008 date time datatypes.

DATEPART and DATENAME have also been augmented - and there's also a new TZoffset 'datepart' - which will let you express timezone offsets as needed.
In fact, pretty-much all of the existing date-time functions have been augmented to account for the new improvements offered by these new SQL Server 2008 datetime datatypes.

There are even a bevy of cool new functions designed specifically for datetimeoffests - including the SWITCHOFFSET and TODATETIMEOFFSET functions - which let you modify offset times, and translate datetime2 values into datetimeoffsets.

Sadly though, one area where it would have been nice to see similar improvements would have been with the CONVERT function - which is really handy for serializing date and time output for use in reports and by other applications.

But there sadly aren't any new features that let this function account for some of the new precisions and capabilities introduced in SQL Server 2008.

Therefore, if you need to serialize these values on a regular basis, you'll probably want to create your own UDFs that use the DATEPART and DATENAME functions instead.

Otherwise, in terms of converting these new datatypes from one type to another, you'll basically find that implicit conversion works pretty well and pretty much as you would anticipate - meaning that in some cases if there's enough disparity between the types of data being converted you will see some coercion, and, likewise, any time you're converting a less precise value to a more precise value, there's really no way that SQL Server will be able to automatically fill in any additional precision on its own.

That said, the best way to get a feeling for how to work with these new changes is just to jump in and start playing around with them.

And, if you'd like, you can download the code accompanying this video in order to review the examples displayed in this video - but Books Online also provides a lot of great examples that will help you quickly get up to speed when using these new datatypes.

Otherwise, to learn more about SQL Server 2008, make sure to check out the other videos in this series, and to stay apprised of new releases, make sure to subscribe to SSV's RSS feed.

And, with that, thanks for joining, and we look forward to seeing you in subsequent SQL Server Videos.