1. The series so far:

   1. SQL Server Graph Databases - Part 1: Introduction
   2. SQL Server Graph Databases - Part 2: Querying Data in a Graph Database
   3. SQL Server Graph Databases - Part 3: Modifying Data in a Graph Database
   4. SQL Server Graph Databases - Part 4: Working with hierarchical data in a graph database
   5. SQL Server Graph Databases - Part 5: Importing Relational Data into a Graph Database
   Failure #1: Assuming that SQL Server stores date/time values as formatted strings
2. Failure #2: Forgetting about those people living in other parts of the world
3. Failure #3: Again forgetting about those people living in other parts of the world
4. Failure #4: Treating DATETIME2 as nothing more than DATETIME with precision
5. Failure #5: Ignoring how SQL Server rounds date/time data
6. Failure #6: Doing a lousy job removing the time from the date
7. Failure #7: Not understanding how the DATEDIFF function works
8. Failure #8: Being careless with your search conditions
9. Failure #9: Forgetting about data type year limitations
10. Failure #10: Not taking full advantage of SQL Server’s date/time functions

Failure #1: Assuming that SQL Server stores date/time values as formatted strings

Many of the failures that surround handling date/time values often result from not understanding how SQL Server stores these values. (It doesn’t help that SQL Server documentation tends not to go too deeply into this topic.)

Beginning T-SQL developers often assume that date/time values are stored as readable input, such as 05-07-2015 10:05:23.187. This is not the case. It would be more accurate to say that SQL Server stores the data as one or more integers, depending on the data type. Some sources suggest the data is stored as floats, but the concept is the same. We’re talking about numbers being stored, not formatted strings.

Let’s start with the DATETIME data type. According to SQL Server documentation, the database engine stores a DATETIME value as two integers. The first integer represents the day and the second integer represents the time. The days can range from January 1, 1753, through December 31, 9999, and the times can range from 00:00:00.000 through 23:59:59.997, with the default value being 1900-01-01 00:00:00.000.

The default value is particularly important when it comes to the date. January 1, 1900 is considered day 0. Earlier dates are represented by negative integers and later dates by positive integers. For example, January 1, 1899 is day -365, and January 1, 1901 is day 365. As for the time portion, SQL Server starts with 0 and increments the numeric value for each .003 seconds after midnight. That means the time 00:00:00.003 is stored as 1, and the time 00:00:01.000 is stored as 300.

This can all seem confusing at first because when retrieving a DATETIME value because we see something much different. For example, let’s start with a simple DATETIME variable:

As we’ve come to expect, the SELECT statement returns the value in the following format:

To get at the actual value, we must first convert it to the VARBINARY type:

Not surprisingly, our results now look quite different:

Because SQL Server stores a DATETIME value as two integers, the storage size is 8 bytes (4 bytes each). The first 4 bytes (0000A491) represent the date, and the last 4 bytes (00A6463C) represent the time. Knowing this, we can use the SUBSTRING function to return only the date or time, as shown in the following example:

Now the SELECT statement returns only the bytes that represent the date integer:

We can do the same thing for the time portion, and we can also convert the VARBINARY values to an INT values to see the actual number. Let’s put it all together to get an overview of how our original DATETIME value is stored:

The following table shows the results returned by the SELECT statement:

DateBinary	DateInt	TimeBinary	TimeInt
0x0000A491	42129	0x00A6463C	10896956

What the results demonstrate is that 42,129 days have passed since January 1, 1900 and over 10 million fractional seconds have ticked away since midnight. Now lets set the clock back about 188 years:

This time our day integer come in at 26,327 days before January 1, 1900, and our time integer clocks in at over 24 million ticks:

DateBinary	DateInt	TimeBinary	TimeInt
0xFFFF9929	-26327	0x016EB86D	24033389

Now let’s set the date and time to the default (day 0):

As expected, the VARBINARY and INT values all zero out:

DateBinary	DateInt	TimeBinary	TimeInt
0x00000000	0	0x00000000	0

Just to make sure this is all clear, here’s one more DATETIME example, one day and one second after the default:

At the point, the results should be fairly predictable. We have a date integer of 1 and a time integer of 300:

DateBinary	DateInt	TimeBinary	TimeInt
0x00000001	1	0x0000012C	300

This should give you a fairly good idea how the database engine stores DATETIME values. However, SQL Server takes a somewhat different approach with other date/time data types. Let’s look at the DATETIME2 data type, configured at the default precision (7):

This time our results look a bit different from the DATETIME value:

For the DATETIME2 data type, SQL Server uses the first byte to store the time precision (07), the last three bytes to store the date (EC390B), and everything in between to store the time (B4854E9254), which can vary in length depending upon the specified precision. The DATE and TIME data types work the same way when storing their portion of the value. For example, if we save the same value to a DATE variable:

Our results match those of the date portion returned in the previous example:

And it works the same way for the TIME data type:

The results match those of the time portion returned by the DATETIME2 example:

Now let’s change the data type precision and time value in the DATETIME2 example to demonstrate the how the time portion can change:

The SELECT statement returns the following results:

Notice that the first byte reflects the specified precision (04) and that there are fewer time-related bytes (01000000). Unfortunately, the logic that SQL Server uses to store the date and time components of DATETIME2, DATE, and TIME is not as straightforward as with DATETIME, and digging into that logic is well beyond the scope of this article, but you can at least pick out the bytes that represent the date and time and have some sense of what is going on.

The point of all this is not to memorize the exact mechanisms SQL Server uses to store date/time data for each data type, but rather to have an overall sense of how the data is stored and that it is not simple strings that contain dates written in the prescribed formats. Having this foundation can help you better address the various issues that might arise when working with date/time values.

Failure #2: Forgetting about those people living in other parts of the world

T-SQL might ostensibly be accepted as a universal language, at least within certain database spheres, but SQL Server settings are not. Quite often, an installed instance is configured to best serve its local users. Where this often becomes particularly apparent is when handling date/time data. Although SQL Server stores the data as one or more integers, it seems to think in terms of string values, converting back and forth from integers to readable formats behind the scenes so we don’t have to deal with dates that look something like 15481099 or times that like 24033389.

To this end, SQL Server includes a number of settings and rules that determine how to interpret values submitted as date/time data. Let’s start with a few examples. In the first one, we set the language to British and convert a VARCHAR value to DATETIME:

As expected, the SELECT statement returns the following results:

Now let’s set the language to US English and then try to convert the value:

This time the database engine baulks and returns the following error:

The problem is in how the inputted date is formatted: day-month-year. This works fine when SQL Server is configured for the British language, but not for US English. When the US English instance of SQL Server sees the data, it assumes that we’re trying to pass in the 19 as a month, rather than a day. We can resolve this issue by submitted a value that is more in line with US expectations:

Now our SELECT statement will return the results just fine. If we were to change the language back to British, but submit the same value, we would again receive the out-of-range error.

Of course, we could reset the language once again, but this is not a particularly effective solution when trying to implement a global application. A better strategy is to ensure that our date/time values are more universal in nature, such as using a numeric format (year-month-day). For example, suppose we try to pass in the date/time value in a numeric format with hyphens:

The numeric format is considered to be a more universal way to pass in time-date data. After all, that’s how SQL Server returns the data. The format allows for date values that use dashes, slashes, or periods to separate the components, just as long as the values follow the year-month-day structure. However, despite the universal nature of this format, the SELECT statement once again returns an out-of-range error:

But look at what happens if we stick with the same language and same format, but this time convert the data to DATETIME2:

The SELECT statement now converts the date with no problem and returns the following results:

It turns out that the numeric format is still subject to the whims of SQL Server’s language and format settings when it comes to the DATETIME data type, but not DATETIME2.

If we stick with the DATETIME2 data type, we can avoid the language issue when using a numeric format, which is a fine option if all we’re running is SQL Server 2008 or later. But not everyone has this luxury. What we need is a format that is both type and language agnostic. For this reason, many developers default to a format such as ISO8601:

This time, the statement runs without an error. By using a universal format for our time/date values, we can better ensure that we get the results we want whether we’re in Seattle or Cambridge or Rome.

Failure #3: Again forgetting about those people living in other parts of the world

Our date formats are not the only place where we can get into trouble when implementing an application that spans multiple geographic locations. Time zones can also present their own challenges if we require a reliable means to track data over a period of time across multiple regions.

One of the problems is that most SQL Server date/time data types are fairly ambiguous. For example, suppose we have a table in a database that tracks security-related events, and one of the rows shows an event occurring on May 15, 2015 at 3:30 in the morning. Is that the time on a local machine? The server’s time? Is SQL Server configured to use a time other than the local time? Is the value in Coordinated Universal Time (UTC)? Without some mechanism in place to provide context, the value is almost meaningless.

In many cases, this might not be a problem, but for systems monitoring or recording critical processes, the exact time is essential. Imagine if an international bank has been hacked, with funds siphoned out of its accounts, and investigators having no way of knowing exactly when the event occurred. And what about time-sensitive analytics? Making sense of reams of historical data can become even more complex if it spans times zones with no mechanism in place to provide context to the data/time values, such as noting in what time zone the data was entered.

Even if such mechanisms are put into place, it’s often up to the developers to implement solutions that take time zones into account, with no consistent approach from one application to the next (or sometimes within a single application).

Fortunately, SQL Server 2008 introduced the DATETIMEOFFSET data type to make managing date/time data a little easier. The data type stores data/type values similarly to how a DATETIME2 value is stored, with a couple bytes tagged on to track the time zone, in relation to UTC. Consider the following example:

The SYSDATETIMEOFFSET system function returns the current date and time as a DATETIMEOFFSET value, which means it includes the date, time, and UTC offset value, as the following results show:

In this case, the date/time value is seven hours behind the UTC, putting us on the US West Coast. If we want, we can instead use the DATENAME system function to retrieve only the offset value:

As expected, the SELECT statement returns only the difference from UTC:

We can further demonstrate how the DATETIMEOFFSET data type works by comparing it to its UTC counterpart:

As the following results show, the UTC date and time are seven hours ahead of the Pacific date and time.

UTC	PDT
2015-05-09 00:57:37.1820000 +00:00	2015-05-08 17:57:37.1820000 -07:00

Since the release of SQL Server 2008, we’ve also had the SWITCHOFFSET system function to change a DATETIMEOFFSET value to a different time zone:

In this case, we’re simply changing the UTC offset value from -07:00 to -05:00 when we retrieve the data, as shown in the following results:

Clearly, SQL Server has made working with time zones much easier, and there is no reason not to take advantage of these features, as long as you’re running SQL Server 2008 or later. However, there is one challenge that SQL Server has not been able to solve: Daylight Saving Time (DST).

Let’s look at what happens when we compare time zones between Melbourne and Seattle on April 1, 2015:

Both Melbourne and Seattle are on DST at this point, giving us an 18-hour difference between the two. However, let’s compare May 1, using the same UTC offset values:

We again see an 18-hour difference, when in fact it should be 17 because Melbourne returned to standard time on April 5. The offset value we should have used for Melbourne on May 5 is +10:00.

Although this is a very simple example, it points to the larger problem of trying to track DST across the globe. Not all regions implement DST, and those that do don’t necessarily implement it at the same time. In addition, the start and end dates change from one year from the next. And those dates become even less predictable by the fact that the laws determining when and if DST is implemented can change.

Add into this fact that time zones themselves can vary greatly even within a region or country. Consider the state of Arizona in the US. Most of the state does not observe DST. That means part of the year they are in sync with Colorado, and the rest of the year they share the same time as California.

What all this points to is that you cannot depend on DATETIMEOFFSET alone to ensure the reliability your date/time values. Varying time zones and unmanageable DST patterns make time zones a challenge with no easy solution. If your applications and analytics rely on pinpointing exact date and time measurements, then the application will need to contain the logic necessary to handle all the variations in data.

Failure #4: Treating DATETIME2 as nothing more than DATETIME with precision

Although DATETIME2 has been around since SQL Server 2008, many developers have been slow to embrace it, sticking with DATETIME more out of habit than anything else. But DATETIME2 offers a number of advantages over DATETIME, in addition to the larger decimal values.

Let’s start by looking at the two in action:

The DATETIME2 data type supports up to seven decimal places for its time component, whereas DATETIME supports only three, which gives us the following results.

DateTime2Type	DateTimeType
2015-05-12 09:47:12.4556789	2015-05-12 09:47:12.457

The first thing worth noting is that SQL Server rounds the time portion of the DATETIME value to the nearest .003 seconds, with values rounded to increments of .000, .003. or .007 seconds. The DATETIME2 data type is much more precise in the regard. Although a value will be rounded if it exceeds the seven decimal places, no rounding occurs if the value is within the limit. For example, .555678999 is rounded to .5556790, but a value such as .9999999 is not rounded at all.

So in this regard, DATETIME2 is also more precise than DATETIME. In addition, you can control the DATETIME2 precision, also unlike DATETIME. For example, the following T-SQL limits the time portion of the DATETIME2 value to a precision of 3:

As you can see in the following results, the DATETIME2 value now includes only three decimal places, just like the DATETIME value.

DateTime2Type	DateTimeType
2015-05-12 09:47:12.556	2015-05-12 09:47:12.557

Once again, the DATETIME2 time portion is rounded because the submitted value exceeded the specified precision, but even this rounding is more precise than what we get with DATETIME. And here’s another interesting part to all this. Although both values take three decimal places, SQL Server uses only 7 bytes to store the DATETIME2 value, but 8 bytes to store the DATETIME value.

In fact, a DATETIME2 value uses 8 bytes only if the precision is greater than 4, and uses only 6 bytes if the precision is less than 3. Not only do you can more precision with DATETIME2, but you can also save storage space, which can be a particularly important consideration when bringing lots of data into memory.

The DATETIME2 data type also has the advantage of being able to remove the decimal places altogether:

Being able to zero out the decimals is a nice feature when you don’t need the microseconds. Now our results are much cleaner:

DateTime2Type	DateTimeType
2015-05-12 09:47:13	2015-05-12 09:47:12.557

Another important consideration when comparing DATETIME2 to DATETIME is that the DATETIME2 data type supports a much wider range of dates. The DATETIME2 data type goes all the way back to January 1, 0001, whereas the DATETIME data type goes back only to January 1, 1753. (They both go to the year 9999, which I’m sure is a comfort to many.)

Also worth noting about the DATETIME2 data type is that we also get the DATE and TIME data types, which offer the same level of precision

As you can see in the following results, the DATE and TIME data types are a handy addition to better management of date/time data:

DateType	TimeType
2015-05-12	09:47:12.5556789

Legacy applications and systems are of course a consideration when implementing the new data types, but as you build new systems, there is often no reason not to use these types, unless you’re working on versions of SQL Server that predate 2008 or working with technologies that cannot handle DATETIME2 values. The DATETIME2 data type and other new types offer too many advantages to ignore, including being better aligned with the .NET date/time types. And as pointed out earlier, DATETIME2 is also more forgiving when it comes to the data/time formats you pass into the database. Clearly, it’s time to break old habits and let the DATETIME data type go.

Failure #5: Ignoring how SQL Server rounds date/time data

In the previous failure, we touched upon rounding, but it is a topic that deserves more than just a brief mention, especially as it concerns the DATETIME and SMALLDATETIME data types. But first, let’s look at what happens when we round DATETIME2 data:

In his case, the DATETIME2 precision is the default 7, so that is the number of decimal places for each value. As the following results show, the @a value does not round the data in any way, but the @b and @c values do:

OrigValue	StoredValue
2015-05-12 23:32:12.1234567	2015-05-12 23:32:12.1234567
2015-05-12 23:32:12.123456789	2015-05-12 23:32:12.1234568
2015-05-12 23:59:59.999999999	2015-05-13 00:00:00.0000000

The @b value is rounded as we would expect. Nine digits are pared down to seven digits, with the value 123456789 rounded up to 1234568. The @c value also follows similar logic. However, because we normally round up under these circumstances, we move onto the next day. In both cases, SQL Server is following very predictable logic. Although the possibility exists for a value to get bumped up to the next day, it still follows along expected lines.

Now lets look what happens with a few DATETIME values:

The @a value gets rounded up, the @b value gets rounded down, and the @c value flies over to the next day, as shown in the following results:

OrigValue	DatetimeValue
2015-05-12 23:59:59.996	2015-05-12 23:59:59.997
2015-05-12 23:59:59.998	2015-05-12 23:59:59.997
2015-05-12 23:59:59.999	2015-05-13 00:00:00.000

What’s surprising about all this rounding is that the values we pass in do not exceed the data type’s precision, yet the rounding occurs anyway. As pointed out earlier, SQL Server stores DATETIME data in increments of .000, .003, and .007 seconds. This can be problematic for analytic processes that require a high degree of precision. This is even more problematic when the date value cannot be relied on to be accurate because the possibility exists for a value to be rounded up to the next day.

The likelihood of losing a day might seem slim, but it can happen in unexpected ways. For example, suppose we want to convert a DATETIME2 value to a DATETIME value:

Because the original precision exceeds what DATETIME can handle, more rounding occurs, once again jumping to the next day:

Datetime2Value	DatetimeValue
2015-05-12 23:59:59.9986789	2015-05-13 00:00:00.000

We can run into even more confusing issues with the SMALLDATETIME data type:

The SMALLDATETIME is accurate only to the nearest minute, despite the fact that the returned values always show 00 seconds:

OrigValue	SmalldatetimeValue
2015-05-12 23:22:22	2015-05-12 23:22:00
2015-05-12 23:22:30	2015-05-12 23:23:00
2015-05-12 23:22:52	2015-05-12 23:23:00
2015-05-12 23:59:52	2015-05-13 00:00:00

The rounding of the @a value is fairly straightforward. The 22 seconds are rounded down, so the minute value remains unchanged. The @b value is rounded up because SQL Server rounds 30 seconds or more up to the next minute, which is also the case for the @c value. The @d value, however, flips over to the next day because the 59 minute also get rounded up, causing the 23 hours to get rounded up.

Now look at what happens if we add fractional seconds to the mix:

Once again, we flip over to the next day:

You’ve got to be wary when it comes to the DATETIME and SMALLDATETIME data types, or you’ll end up with data that will throw off your results in unexpected ways. Whenever you can make DATETIME2 work, that’s the way to go.

Failure #6: Doing a lousy job removing the time from the date

Often you’ll find that you’re not interested in the time portion of a date/time value and want to zero out the time or get rid of it altogether. Prior to SQL Server 2008, you had to work a little harder to get at the date, but now we have the DATE data type to make our lives easier:

In this case, we simply convert the DATETIME2 value to a DATE value, and everything works out great, as shown in the following results.

We can just as easily convert a DATETIME value to a DATE value and get the same results:

In fact, we can even convert our original value to the TIME data type:

As expected, the SELECT statement now returns only the time:

Before we go any further, be sure to make note of the DATE and TIME data types. If they’re available for you to use in your particular situation, then use them, and forget the rest of this section. They are your best solutions for getting at the data you want from your date/time values.

However, if you don’t have the luxury of simply converting your data in this way, then you must seek out other means. One approach that has been suggested is to convert the data:

As you can see, we convert the date to a string, using an ISO format (112), and then convert it back to a DATETIME value, giving us the following results:

Although this solution will work, it does not make the database engine happy. For one or two rows it’s no big deal, but imagine if you’re converting data in millions of rows. A better solution is to take advantage of the DATEADD and DATEDIFF system functions to zero out those dates:

What we’re doing here is calculating the number of days between day 0 (January 1, 1900) and our inputted date, and then adding the difference to day 0, once again giving us the following results:

In this way, the database engine is happy because it can take advantage of the inherent integer nature of the DATETIME data type, and we still get the results we want.

A similar approach is to provide a specific date from which to work, rather than use day 0:

This again returns the results we want by calculating the difference between January 1, 2001 and the inputted date. The fun part about this approach is that we can use it in other ways. For example, we can tweak our statement to return the first day of the month, relative to the inputted value:

All we’ve done is change the DAY arguments to MONTH to get the following results:

If we change the date specified in the DATEADD function to one day earlier than our base date, we can get the last day of the previous month:

Now the SELECT statement returns the following results:

Even if you have the DATE and TIME data types available to you, these last two examples can be handy approaches to keep in mind for retrieving the data you need.

Failure #7: Not understanding how the DATEDIFF function works

While we’re on the subject of DATEDIFF, it’s worth taking a closer look. If we’re not careful when using the function, we can end up with results not quite as accurate as we might hope. Suppose we are trying to determine the number of minutes between two date/time values:

In this case, the SELECT statement returns a value of 1, a full minute, even though there is only a one-second difference between these two dates. The same thing happens if we try to find the hour difference between the values:

This time the SELECT statement indicates that there is a one-hour difference between the values, rather than one second. Same thing happens with months:

Again, we’re told that there is a one-month difference between the dates. If we looked for years, we would get similar results, yet we’re still talking about only one second.

The problem is not with the DATEDIFF function itself, but rather with our understanding of how it works. When we specify a date part, whether year, month, hour, or minute, the database engine looks no further than that part. So if we specify month, the engine compares the years and months, but nothing further. The values might be only one second apart, but all the engine cares about are the years and months.

One way to get around this is to go at least one level deeper than we need and then divide our way back to the level we’re looking for. For example, suppose we want to get at the number of minutes between the two values. We can instead retrieve the number of seconds, and then divide by 60 to get a more accurate view of the minutes, as shown in the following example:

Now the SELECT statement returns 0.0166666, rather than 1, which is much closer to the truth.

Failure #8: Being careless with your search conditions

Referencing date/time values in your search conditions can be tricky business if not done carefully. Without exercising due caution, you can end up retrieving the wrong data or, worse still, updating and deleting the wrong data.

To demonstrate why this might be an issue, let’s first create a temporary table and populate it with several rows that include DATETIME2 data:

Not let’s try to select the rows for May 7, 2015:

As the following results show, our query returns only one row, when we want to be seeing two:

ColA	ColB
103	2015-05-07 00:00:00.000

The challenge with trying to retrieve the data in this way is that the DATETIME2 data type, like other date/time data types, store both the date and time, with the time often being something other than midnight (all zeroes). However, when we compare a date-only value to a value in a date/time column, SQL Server uses midnight for the date-only value. As a result, a comparison such as the one in the WHERE clause above will filter out all rows except those with a matching date and with midnight for the time.

One way to get around this is to convert the column data to match the date-only value:

Now the SELECT statement returns the results we want:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.321

The problem with this approach is that, as mentioned earlier, the database engine is not fond of such conversions. The query engine can’t use indexes effectively nor take advantage of the inherent integer nature of the date/time data type. Because of these issues, some might turn to the BETWEEN operator to retrieve the day’s data:

This time, however, we get more rows that we bargain for:

ColA	ColB
101	2015-05-06 22:43:55.123
102	2015-05-06 23:59:59.997
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.321
105	2015-05-08 00:00:00.000

The challenge here is that the BETWEEN operator is inclusive by nature, which means it wants to bring in at least some of the rows for the beginning and end dates. To get around this, we might consider using BETWEEN with only the date we want to include:

Once again the SELECT statement is back to returning only one row:

ColA	ColB
103	2015-05-07 00:00:00.000

The problem this time is similar to using WHERE ColB = '2015-05-07'. The BETWEEN operator is basing its calculations on the entire value, including the time, so our WHERE clause essentially looks like the following:

Of course, this will return only the same one row. However, we can address this issue by being more precise with our end date:

Now the SELECT statement returns the results we want:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.321

Although this approach works fine for the DATETIME2 data type, we can simplify the code by instead using equality operators to define our search condition:

The SELECT statement once again returns the results we want, while keeping our query simple. This approach is also the best way to go when working with the DATETIME data type. Let me demonstrate. Suppose we had originally defined ColB with the DATETIME data type and populated the table accordingly:

Now let’s use the BETWEEN operator with our precise data range to retrieve the data:

This time, our SELECT statement returns three rows:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.320
105	2015-05-08 00:00:00.000

Because we’re comparing our BETWEEN dates with a DATETIME value, the database engine rounds the values we enter to conform to the DATATIME limitations. This causes our end date to flip over to the next day, resulting in the first row for that day being included in the result set. To get around this, we can again turn to the equality operators:

Now the SELECT statement returns the expected two rows:

ColA	ColB
103	2015-05-07 00:00:00.000
104	2015-05-07 17:33:36.320

Regardless of the type of time/date data types we’re working with, using equality operators to define our search condition remains the best approach.

Failure #9: Forgetting about data type year limitations

This might seem like a no-brainer, but it’s worth a reminder. If you’re converting data from one date/time type to another, you must keep the permitted date ranges in mind. Case in point:

We’re trying to convert a DATETIME2 value for the year 1623 to a DATETIME value. Unfortunately, the DATETIME data type supports only the years 1753 through 9999. Although this is a good thing for those with an unwavering hope in the future, it’s not such good news for those with a penchant for historical trivia or who want to run queries similar to the one shown above, which lead to results similar to the following:

The message should be self-evident. The DATETIME data type has no interest in the year 1623. The SMALLDATETIME data type is even more limited:

The SELECT statement will once again return an out-of-range error because the SMALLDATETIME data type supports only the years 1900 through 2079. When converting data from one date/time type to another, be sure to keep these limitations in mind.

Failure #10: Not taking full advantage of SQL Server’s date/time functions

SQL Server 2008 added great built-in functions for working with date/time data, and it would be a shame not to take full advantage of them. For some, however, that will mean imagining a world beyond GETDATE or GETUTCDATE.

Let’s look at some of the date/time functions in action:

As you can see in the following results, we have a variety of options from which to choose:

The SYSDATETIME function returns the current date and time as a DATETIME2 value. The SYSUTCDATETIME function returns the same data, but as a UTC value. The SYSDATETIMEOFFSET function returns the current date and time as a DATETIMEOFFSET value, which means we also get the offset amount.

SQL Server 2008 also included enhancements to the DATENAME and DATEPART functions to provide support for the newer date/time data types. The functions now include date part options for microseconds, nanoseconds, and UTC offsets. The following example shows the new date parts used in the DATENAME function:

And here are our results:

Microseconds	Nanoseconds	TimezoneOffset
904672	904672200	-07:00

The DATEPART function works pretty much the same way:

Here are the results this time around:

Microseconds	Nanoseconds	TimezoneOffset
904672	904672200	-420

The results are fairly similar to DATENAME, except for the offset value, which is in minutes, rather than hours.

You should become familiar with all the date/time functions because they can enhance your ability to work with date/time data more effectively, especially as you incorporate the newer date/time types.