{"id":2002,"date":"2015-05-28T00:00:00","date_gmt":"2015-05-28T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-get-sql-server-dates-and-times-horribly-wrong\/"},"modified":"2021-09-29T16:21:26","modified_gmt":"2021-09-29T16:21:26","slug":"how-to-get-sql-server-dates-and-times-horribly-wrong","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/how-to-get-sql-server-dates-and-times-horribly-wrong\/","title":{"rendered":"How to Get SQL Server Dates and Times Horribly Wrong"},"content":{"rendered":"
\n
    \n

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

      Failure #1: Assuming that SQL Server stores date\/time values as formatted strings<\/h2>\n

      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.)<\/p>\n

      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.<\/p>\n

      Let’s start with the DATETIME<\/code><\/b> data type. According to SQL Server documentation, the database engine stores a DATETIME<\/code><\/b> 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.<\/p>\n

      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.<\/p>\n

      This can all seem confusing at first because when retrieving a DATETIME<\/code><\/b> value because we see something much different. For example, let’s start with a simple DATETIME<\/code><\/b> variable:<\/p>\n

      DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\r\nSELECT @a;\r\n<\/pre>\n
      As we’ve come to expect, the  SELECT<\/b> statement returns the value in the following format:<\/p>\n
      2015-05-07 10:05:23.187\r\n<\/pre>\n
      To get at the actual value, we must first convert it to the VARBINARY<\/code><\/b> type:<\/p>\n
      DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\r\nSELECT CONVERT(VARBINARY(8), @a);\r\n<\/pre>\n
      Not surprisingly, our results now look quite different:<\/p>\n
      0x0000A49100A6463C\r\n<\/pre>\n
      Because SQL Server stores a  DATETIME<\/b> value as two integers, the storage size is 8 bytes (4 bytes each). The first 4 bytes (0000A491<\/b>) represent the date, and the last 4 bytes (00A6463C<\/code><\/b>) represent the time. Knowing this, we can use the  SUBSTRING<\/b> function to return only the date or time, as shown in the following example:<\/p>\n
      DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\r\nSELECT SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4);\r\n<\/pre>\n
      Now the  SELECT<\/code><\/b> statement returns only the bytes that represent the date integer:<\/p>\n
      0x0000A491\r\n<\/pre>\n
      We can do the same thing for the time portion, and we can also convert the VARBINARY<\/code><\/b> values to an  INT<\/code><\/b> values to see the actual number. Let’s put it all together to get an overview of how our original DATETIME<\/code><\/b> value is stored:<\/p>\n
      DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\r\nSELECT \r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\r\n<\/pre>\n
      The following table shows the results returned by the SELECT<\/b> statement:<\/p>\n\n\n\n\n
      \n
       DateBinary<\/b><\/p>\n<\/td>\n
      		\n
      DateInt<\/b><\/p>\n<\/td>\n
      		\n
       TimeBinary<\/b><\/p>\n<\/td>\n
      		\n
      TimeInt<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      0x0000A491<\/p>\n<\/td>\n
      		\n
      42129<\/p>\n<\/td>\n
      		\n
      0x00A6463C<\/p>\n<\/td>\n
      		\n
      10896956<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      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:<\/p>\n
      DECLARE @a DATETIME = '1827-12-03 22:15:11.297'\r\nSELECT \r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\r\n<\/pre>\n
      This time our day integer come in at 26,327 days before<\/i> January 1, 1900, and our time integer clocks in at over 24 million ticks:<\/p>\n\n\n\n\n
      \n
       DateBinary<\/b><\/p>\n<\/td>\n
      		\n
      DateInt<\/b><\/p>\n<\/td>\n
      		\n
       TimeBinary<\/b><\/p>\n<\/td>\n
      		\n
      TimeInt<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      0xFFFF9929<\/p>\n<\/td>\n
      		\n
      -26327<\/p>\n<\/td>\n
      		\n
      0x016EB86D<\/p>\n<\/td>\n
      		\n
      24033389<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Now let’s set the date and time to the default (day 0):<\/p>\n
      DECLARE @a DATETIME = '1900-01-01 00:00:00.000'\r\nSELECT \r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\r\n<\/pre>\n
      As expected, the VARBINARY<\/code><\/b> and INT<\/code><\/b> values all zero out:<\/p>\n\n\n\n\n
      \n
       DateBinary<\/b><\/p>\n<\/td>\n
      		\n
      DateInt<\/b><\/p>\n<\/td>\n
      		\n
       TimeBinary<\/b><\/p>\n<\/td>\n
      		\n
      TimeInt<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      0x00000000<\/p>\n<\/td>\n
      		\n
      0<\/p>\n<\/td>\n
      		\n
      0x00000000<\/p>\n<\/td>\n
      		\n
      0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Just to make sure this is all clear, here’s one more DATETIME<\/b> example, one day and one second after the default:<\/p>\n
      DECLARE @a DATETIME = '1900-01-02 00:00:01.000'\r\nSELECT \r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\r\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\r\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\r\n<\/pre>\n
      At the point, the results should be fairly predictable. We have a date integer of 1 and a time integer of 300:<\/p>\n\n\n\n\n
      \n
       DateBinary<\/b><\/p>\n<\/td>\n
      		\n
      DateInt<\/b><\/p>\n<\/td>\n
      		\n
       TimeBinary<\/b><\/p>\n<\/td>\n
      		\n
      TimeInt<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      0x00000001<\/p>\n<\/td>\n
      		\n
      1<\/p>\n<\/td>\n
      		\n
      0x0000012C<\/p>\n<\/td>\n
      		\n
      300<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      This should give you a fairly good idea how the database engine stores DATETIME<\/code><\/b> values. However, SQL Server takes a somewhat different approach with other date\/time data types. Let’s look at the DATETIME2<\/code><\/b> data type, configured at the default precision (7):<\/p>\n
      DECLARE @b DATETIME2 = '2015-05-07 10:05:23.1872436'\r\nSELECT CONVERT(VARBINARY(10), @b);\r\n<\/pre>\n
      This time our results look a bit different from the DATETIME<\/b> value:<\/p>\n
      0x07B4854E9254EC390B\r\n<\/pre>\n
      For the  DATETIME2<\/code><\/b> data type, SQL Server uses the first byte to store the time precision (07<\/code><\/b>), the last three bytes to store the date (EC390B<\/code><\/b>), and everything in between to store the time (B4854E9254<\/b>), which can vary in length depending upon the specified precision. The DATE<\/code><\/b> and TIME<\/code><\/b> data types work the same way when storing their portion of the value. For example, if we save the same value to a DATE<\/code><\/b> variable:<\/p>\n
      DECLARE @b DATE = '2015-05-07 10:05:23.1872436'\r\nSELECT CONVERT(VARBINARY(10), @b);\r\n<\/pre>\n
      Our results match those of the date portion returned in the previous example:<\/p>\n
      0xEC390B\r\n<\/pre>\n
      And it works the same way for the  TIME<\/b> data type:<\/p>\n
      DECLARE @b TIME = '2015-05-07 10:05:23.1872436'\r\nSELECT CONVERT(VARBINARY(10), @b);\r\n<\/pre>\n
      The results match those of the time portion returned by the DATETIME2<\/code><\/b> example:<\/p>\n
      0x07B4854E9254\r\n<\/pre>\n
      Now let’s change the data type precision and time value in the DATETIME2<\/code><\/b> example to demonstrate the how the time portion can change:<\/p>\n
      DECLARE @b DATETIME2(4) = '2015-05-07 00:00:00.0001'\r\nSELECT CONVERT(VARBINARY(10), @b);\r\n<\/pre>\n
      The  SELECT<\/code><\/b> statement returns the following results:<\/p>\n
      0x0401000000EC390B\r\n<\/pre>\n
      Notice that the first byte reflects the specified precision (04<\/code><\/b>) and that there are fewer time-related bytes (01000000<\/code><\/b>). Unfortunately, the logic that SQL Server uses to store the date and time components of DATETIME2<\/code><\/b>, DATE<\/code><\/b>, and TIME<\/code><\/b> is not as straightforward as with  DATETIME<\/code><\/b>, 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.<\/p>\n
      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.<\/p>\n
      Failure #2: Forgetting about those people living in other parts of the world<\/h2>\n
      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<\/code><\/b> or times that like  24033389<\/code><\/b>.<\/p>\n
      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<\/code><\/b> value to DATETIME<\/code><\/b>:<\/p>\n
      SET LANGUAGE british;\r\nDECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';\r\nSELECT CAST(@a AS DATETIME);\r\n<\/pre>\n
      As expected, the SELECT<\/code><\/b> statement returns the following results:<\/p>\n
      2015-05-19 10:11:12.000\r\n<\/pre>\n
      Now let’s set the language to US English and then try to convert the value:<\/p>\n
      SET LANGUAGE us_english;\r\nDECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';\r\nSELECT CAST(@a AS DATETIME);\r\n<\/pre>\n
      This time the database engine baulks and returns the following error:<\/p>\n
      Msg 242, Level 16, State 3, Line 730\r\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\r\n<\/pre>\n
      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<\/b> as a month, rather than a day. We can resolve this issue by submitted a value that is more in line with US expectations:<\/p>\n
      SET LANGUAGE us_english;\r\nDECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';\r\nSELECT CAST(@a AS DATETIME);\r\n<\/pre>\n
      Now our  SELECT<\/code><\/b> 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.<\/p>\n
      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:<\/p>\n
      SET LANGUAGE british;\r\nDECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';\r\nSELECT CAST(@a AS DATETIME);\r\n<\/pre>\n
      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<\/code><\/b> statement once again returns an out-of-range error:<\/p>\n
      Msg 242, Level 16, State 3, Line 204\r\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\r\n<\/pre>\n
      But look at what happens if we stick with the same language and same format, but this time convert the data to  DATETIME2<\/b>:<\/p>\n
      SET LANGUAGE british;\r\nDECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';\r\nSELECT CAST(@a AS DATETIME2);\r\n<\/pre>\n
      The  SELECT<\/code><\/b> statement now converts the date with no problem and returns the following results:<\/p>\n
      2015-05-19 10:11:12.0000000\r\n<\/pre>\n
      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<\/b> data type, but not DATETIME2<\/b>.<\/p>\n
      If we stick with the DATETIME2<\/code><\/b> 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:<\/p>\n
      SET LANGUAGE british;\r\nDECLARE @a VARCHAR(20) = '2015-05-19T10:11:12';\r\nSELECT CAST(@a AS DATETIME);\r\n\r\n<\/pre>\n
      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.<\/p>\n
      Failure #3: Again forgetting about those people living in other parts of the world<\/h2>\n
      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.<\/p>\n
      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.<\/p>\n
      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.<\/p>\n
      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).<\/p>\n
      Fortunately, SQL Server 2008 introduced the  DATETIMEOFFSET<\/b> data type to make managing date\/time data a little easier. The data type stores data\/type values similarly to how a  DATETIME2<\/code><\/b> value is stored, with a couple bytes tagged on to track the time zone, in relation to UTC. Consider the following example:<\/p>\n
      DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();\r\nSELECT @pdt;\r\n<\/pre>\n
      The  SYSDATETIMEOFFSET<\/code><\/b> system function returns the current date and time as a  DATETIMEOFFSET<\/b> value, which means it includes the date, time, and UTC offset value, as the following results show:<\/p>\n
      2015-05-08 17:57:23.5350000 -07:00\r\n<\/pre>\n
      In this case, the date\/time value is seven hours behind the UTC<\/code><\/b>, putting us on the US West Coast. If we want, we can instead use the  DATENAME<\/b> system function to retrieve only the offset value:<\/p>\n
      DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();\r\nSELECT DATENAME(tzoffset, @pdt);\r\n<\/pre>\n
      As expected, the SELECT<\/code><\/b> statement returns only the difference from UTC:<\/p>\n
      -07:00\r\n<\/pre>\n
      We can further demonstrate how the  DATETIMEOFFSET<\/b> data type works by comparing it to its UTC counterpart:<\/p>\n
      DECLARE @utc DATETIMEOFFSET = SYSUTCDATETIME(),\r\n\u00a0 @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();\r\nSELECT @utc AS UTC, @pdt AS PDT;\r\n<\/pre>\n
      As the following results show, the UTC date and time are seven hours ahead of the Pacific date and time.<\/p>\n\n\n\n\n
      \n
      UTC<\/b><\/p>\n<\/td>\n
      		\n
      PDT<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-09 00:57:37.1820000 +00:00<\/p>\n<\/td>\n
      		\n
      2015-05-08 17:57:37.1820000 -07:00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Since the release of SQL Server 2008, we’ve also had the SWITCHOFFSET<\/code><\/b> system function to change a DATETIMEOFFSET<\/code><\/b> value to a different time zone:<\/p>\n
      DECLARE @a DATETIMEOFFSET = '2015-05-08 17:57:53.3390000 -07:00';\r\nSELECT SWITCHOFFSET(@a, '-05:00');\r\n<\/pre>\n
      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:<\/p>\n
      2015-05-08 19:57:53.3390000 -05:00\r\n<\/pre>\n
      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).<\/p>\n
      Let’s look at what happens when we compare time zones between Melbourne and Seattle on April 1, 2015:<\/p>\n
      DECLARE @mel DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 +11:00',\r\n\u00a0 @sea DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 -07:00';\r\nSELECT DATEDIFF(hh, @mel, @sea);\r\n<\/pre>\n
      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:<\/p>\n
      DECLARE @mel DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 +11:00',\r\n\u00a0 @sea DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 -07:00';\r\nSELECT DATEDIFF(hh, @mel, @sea);\r\n<\/pre>\n
      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.<\/p>\n
      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.<\/p>\n
      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.<\/p>\n
      What all this points to is that you cannot depend on DATETIMEOFFSET<\/b> 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.<\/p>\n
      Failure #4: Treating DATETIME2 as nothing more than DATETIME with precision<\/h2>\n
      Although  DATETIME2<\/code><\/b> has been around since SQL Server 2008, many developers have been slow to embrace it, sticking with DATETIME<\/code><\/b> more out of habit than anything else. But  DATETIME2<\/b> offers a number of advantages over DATETIME<\/code><\/b>, in addition to the larger decimal values.<\/p>\n
      Let’s start by looking at the two in action:<\/p>\n
      DECLARE @a DATETIME2 = '2015-05-12 09:47:12.5556789',\r\n\u00a0 @b DATETIME = '2015-05-12 09:47:12.555';\r\nSELECT @a AS DateTime2Type, @b AS DateTimeType;\r\n<\/pre>\n
      The  DATETIME2<\/code><\/b> data type supports up to seven decimal places for its time component, whereas  DATETIME<\/code><\/b> supports only three, which gives us the following results.<\/p>\n\n\n\n\n
      \n
       DateTime2Type<\/b><\/p>\n<\/td>\n
      		\n
       DateTimeType<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 09:47:12.4556789<\/p>\n<\/td>\n
      		\n
      2015-05-12 09:47:12.457<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The first thing worth noting is that SQL Server rounds the time portion of the DATETIME<\/code><\/b> value to the nearest .003 seconds, with values rounded to increments of .000, .003. or .007 seconds. The DATETIME2<\/code><\/b> 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.<\/p>\n
      So in this regard, DATETIME2<\/code><\/b> is also more precise than DATETIME<\/code><\/b>. In addition, you can control the DATETIME2<\/code><\/b> precision, also unlike  DATETIME<\/code><\/b>. For example, the following T-SQL limits the time portion of the  DATETIME2<\/b> value to a precision of 3:<\/p>\n
      DECLARE @a DATETIME2(3) = '2015-05-12 09:47:12.5556789',\r\n\u00a0 @b DATETIME = '2015-05-12 09:47:12.555';\r\nSELECT @a AS DateTime2Type, @b AS DateTimeType;\r\n<\/pre>\n
      As you can see in the following results, the  DATETIME2<\/b> value now includes only three decimal places, just like the DATETIME<\/code><\/b> value.<\/p>\n\n\n\n\n
      \n
       DateTime2Type<\/b><\/p>\n<\/td>\n
      		\n
       DateTimeType<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 09:47:12.556<\/p>\n<\/td>\n
      		\n
      2015-05-12 09:47:12.557<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Once again, the DATETIME2<\/code><\/b> 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<\/code><\/b>. 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<\/b> value, but 8 bytes to store the DATETIME<\/code><\/b> value.<\/p>\n
      In fact, a  DATETIME2<\/code><\/b> 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<\/code><\/b>, but you can also save storage space, which can be a particularly important consideration when bringing lots of data into memory.<\/p>\n
      The  DATETIME2<\/code><\/b> data type also has the advantage of being able to remove the decimal places altogether:<\/p>\n
      DECLARE @a DATETIME2(0) = '2015-05-12 09:47:12.5556789',\r\n\u00a0 @b DATETIME = '2015-05-12 09:47:12.555';\r\nSELECT @a AS DateTime2Type, @b AS DateTimeType;\r\n<\/pre>\n
      Being able to zero out the decimals is a nice feature when you don’t need the microseconds. Now our results are much cleaner:<\/p>\n\n\n\n\n
      \n
       DateTime2Type<\/b><\/p>\n<\/td>\n
      		\n
       DateTimeType<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 09:47:13<\/p>\n<\/td>\n
      		\n
      2015-05-12 09:47:12.557<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Another important consideration when comparing  DATETIME2<\/b> to  DATETIME<\/b> is that the  DATETIME2<\/b> data type supports a much wider range of dates. The DATETIME2<\/code><\/b> data type goes all the way back to January 1, 0001, whereas the  DATETIME<\/b> 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.)<\/p>\n
      Also worth noting about the  DATETIME2<\/b> data type is that we also get the DATE<\/code><\/b> and TIME<\/code><\/b> data types, which offer the same level of precision<\/p>\n
      DECLARE @a DATE = '2015-05-12 09:47:12.5556789',\r\n\u00a0 @b TIME = '2015-05-12 09:47:12.5556789';\r\nSELECT @a AS DateType, @b AS TimeType;\r\n<\/pre>\n
      As you can see in the following results, the  DATE<\/b> and  TIME<\/b> data types are a handy addition to better management of date\/time data:<\/p>\n\n\n\n\n
      \n
      DateType<\/b><\/p>\n<\/td>\n
      		\n
      TimeType<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12<\/p>\n<\/td>\n
      		\n
      09:47:12.5556789<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      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<\/code><\/b> values. The  DATETIME2<\/code><\/b> 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<\/code><\/b> 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<\/b> data type go.<\/p>\n
      Failure #5: Ignoring how SQL Server rounds date\/time data<\/h2>\n
      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<\/code><\/b> and SMALLDATETIME<\/code><\/b> data types. But first, let’s look at what happens when we round  DATETIME2<\/b> data:<\/p>\n
      DECLARE @a DATETIME2 = '2015-05-12 23:32:12.1234567',\r\n\u00a0 @b DATETIME2 = '2015-05-12 23:32:12.123456789',\r\n\u00a0 @c DATETIME2 = '2015-05-12 23:59:59.999999999';\r\nSELECT '2015-05-12 23:32:12.1234567' AS OrigValue, @a AS StoredValue\r\nUNION ALL\r\nSELECT '2015-05-12 23:32:12.123456789', @b\r\nUNION ALL\r\nSELECT '2015-05-12 23:59:59.999999999', @c;\r\n<\/pre>\n
      In his case, the DATETIME2<\/code><\/b> precision is the default 7, so that is the number of decimal places for each value. As the following results show, the @a<\/code><\/b> value does not round the data in any way, but the  @b<\/b> and  @c<\/b> values do:<\/p>\n\n\n\n\n\n\n
      \n
      OrigValue<\/b><\/p>\n<\/td>\n
      		\n
       StoredValue<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:32:12.1234567<\/p>\n<\/td>\n
      		\n
      2015-05-12 23:32:12.1234567<\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:32:12.123456789<\/p>\n<\/td>\n
      		\n
      2015-05-12 23:32:12.1234568<\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:59:59.999999999<\/p>\n<\/td>\n
      		\n
      2015-05-13 00:00:00.0000000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The  @b<\/code><\/b> value is rounded as we would expect. Nine digits are pared down to seven digits, with the value  123456789<\/code><\/b> rounded up to 1234568<\/code><\/b>. The @c<\/code><\/b> 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.<\/p>\n
      Now lets look what happens with a few  DATETIME<\/b> values:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 23:59:59.996',\r\n\u00a0 @b DATETIME = '2015-05-12 23:59:59.998',\r\n\u00a0 @c DATETIME = '2015-05-12 23:59:59.999';\r\nSELECT '2015-05-12 23:59:59.996' AS OrigValue, @a AS DatetimeValue\r\nUNION ALL\r\nSELECT '2015-05-12 23:59:59.998', @b\r\nUNION ALL\r\nSELECT '2015-05-12 23:59:59.999', @c;\r\n<\/pre>\n
      The  @a<\/code><\/b> value gets rounded up, the @b<\/code><\/b> value gets rounded down, and the @c<\/code><\/b> value flies over to the next day, as shown in the following results:<\/p>\n\n\n\n\n\n\n
      \n
      OrigValue<\/b><\/p>\n<\/td>\n
      		\n
       DatetimeValue<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:59:59.996<\/p>\n<\/td>\n
      		\n
      2015-05-12 23:59:59.997<\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:59:59.998<\/p>\n<\/td>\n
      		\n
      2015-05-12 23:59:59.997<\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:59:59.999<\/p>\n<\/td>\n
      		\n
      2015-05-13 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      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<\/b> 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.<\/p>\n
      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<\/b> value to a  DATETIME<\/b> value:<\/p>\n
      DECLARE @a DATETIME2 = '2015-05-12 23:59:59.9986789';\r\nDECLARE @b DATETIME = @a;\r\nSELECT @a AS Datetime2Value, @b AS DatetimeValue;\r\n<\/pre>\n
      Because the original precision exceeds what  DATETIME<\/b> can handle, more rounding occurs, once again jumping to the next day:<\/p>\n\n\n\n\n
      \n
       Datetime2Value<\/b><\/p>\n<\/td>\n
      		\n
       DatetimeValue<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:59:59.9986789<\/p>\n<\/td>\n
      		\n
      2015-05-13 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      We can run into even more confusing issues with the SMALLDATETIME<\/b> data type:<\/p>\n
      DECLARE @a SMALLDATETIME = '2015-05-12 23:22:22',\r\n\u00a0 @b SMALLDATETIME = '2015-05-12 23:22:30',\r\n\u00a0 @c SMALLDATETIME = '2015-05-12 23:22:52',\r\n\u00a0 @d SMALLDATETIME = '2015-05-12 23:59:52';\r\nSELECT '2015-05-12 23:22:22' AS OrigValue, @a AS SmalldatetimeValue\r\nUNION ALL\r\nSELECT '2015-05-12 23:22:30', @b\r\nUNION ALL\r\nSELECT '2015-05-12 23:22:52', @c\r\nUNION ALL\r\nSELECT '2015-05-12 23:59:52', @d;\r\n<\/pre>\n
      The  SMALLDATETIME<\/code><\/b> is accurate only to the nearest minute, despite the fact that the returned values always show 00 seconds:<\/p>\n\n\n\n\n\n\n\n
      \n
      OrigValue<\/b><\/p>\n<\/td>\n
      		\n
       SmalldatetimeValue<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:22:22<\/p>\n<\/td>\n
      		\n
      2015-05-12 23:22:00<\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:22:30<\/p>\n<\/td>\n
      		\n
      2015-05-12 23:23:00<\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:22:52<\/p>\n<\/td>\n
      		\n
      2015-05-12 23:23:00<\/p>\n<\/td>\n<\/tr>\n
      \n
      2015-05-12 23:59:52<\/p>\n<\/td>\n
      		\n
      2015-05-13 00:00:00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The rounding of the @a<\/code><\/b> value is fairly straightforward. The 22 seconds are rounded down, so the minute value remains unchanged. The @b<\/code><\/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<\/code><\/b> value. The @d<\/code><\/b> value, however, flips over to the next day because the 59 minute also get rounded up, causing the 23 hours to get rounded up.<\/p>\n
      Now look at what happens if we add fractional seconds to the mix:<\/p>\n
      DECLARE @a SMALLDATETIME = '2015-05-12 23:59:29.999';\r\nSELECT @a;\r\n\t<\/pre>\n
      Once again, we flip over to the next day:<\/p>\n
      2015-05-13 00:00:00\r\n<\/pre>\n
      You’ve got to be wary when it comes to the  DATETIME<\/b> and  SMALLDATETIME<\/b> data types, or you’ll end up with data that will throw off your results in unexpected ways. Whenever you can make  DATETIME2<\/code><\/b> work, that’s the way to go.<\/p>\n
      Failure #6: Doing a lousy job removing the time from the date<\/h2>\n
      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<\/code><\/b> data type to make our lives easier:<\/p>\n
      DECLARE @a DATETIME2(3) = '2015-05-12 22:14:18.003';\r\nSELECT CAST(@a AS DATE);\r\n<\/pre>\n
      In this case, we simply convert the  DATETIME2<\/b> value to a  DATE<\/b> value, and everything works out great, as shown in the following results.<\/p>\n
      2015-05-12\r\n<\/pre>\n
      We can just as easily convert a  DATETIME<\/b> value to a  DATE<\/b> value and get the same results:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\r\nSELECT CAST(@a AS DATE);\r\n<\/pre>\n
      In fact, we can even convert our original value to the TIME<\/code><\/b> data type:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\r\nSELECT CAST(@a AS TIME(3));\r\n<\/pre>\n
      As expected, the SELECT<\/code><\/b> statement now returns only the time:<\/p>\n
      22:14:18.003\r\n<\/pre>\n
      Before we go any further, be sure to make note of the DATE<\/b> and  TIME<\/b> 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.<\/p>\n
      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:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\r\nSELECT CAST(CONVERT(CHAR(8), @a, 112) AS DATETIME);\r\n<\/pre>\n
      As you can see, we convert the date to a string, using an ISO format (112), and then convert it back to a DATETIME<\/code><\/b> value, giving us the following results:<\/p>\n
      2015-05-12 00:00:00.000\r\n<\/pre>\n
      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<\/code><\/b> and  DATEDIFF<\/code><\/b> system functions to zero out those dates:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\r\nSELECT DATEADD(DAY, DATEDIFF(DAY, 0, @a), 0);\r\n<\/pre>\n
      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:<\/p>\n
      2015-05-12 00:00:00.000\r\n<\/pre>\n
      In this way, the database engine is happy because it can take advantage of the inherent integer nature of the  DATETIME<\/b> data type, and we still get the results we want.<\/p>\n
      A similar approach is to provide a specific date from which to work, rather than use day 0:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\r\nSELECT DATEADD(DAY, DATEDIFF(DAY, '20010101', @a), '20010101');\r\n<\/pre>\n
      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:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\r\nSELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20010101');\r\n<\/pre>\n
      All we’ve done is change the  DAY<\/b> arguments to  MONTH<\/b> to get the following results:<\/p>\n
      2015-05-01 00:00:00.000\r\n<\/pre>\n
      If we change the date specified in the  DATEADD<\/b> function to one day earlier than our base date, we can get the last day of the previous month:<\/p>\n
      DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\r\nSELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20001231');\r\n<\/pre>\n
      Now the  SELECT<\/code><\/b> statement returns the following results:<\/p>\n
      2015-04-30 00:00:00.000\r\n<\/pre>\n
      Even if you have the DATE<\/code><\/b> and TIME<\/code><\/b> data types available to you, these last two examples can be handy approaches to keep in mind for retrieving the data you need.<\/p>\n
      Failure #7: Not understanding how the DATEDIFF function works<\/h2>\n
      While we’re on the subject of  DATEDIFF<\/b>, 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:<\/p>\n
      DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\r\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\r\nSELECT DATEDIFF(mi, @a, @b);\r\n<\/pre>\n
      In this case, the SELECT<\/code><\/b> 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:<\/p>\n
      DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\r\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\r\nSELECT DATEDIFF(hh, @a, @b);\r\n<\/pre>\n
      This time the SELECT<\/code><\/b> statement indicates that there is a one-hour difference between the values, rather than one second. Same thing happens with months:<\/p>\n
      DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\r\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\r\nSELECT DATEDIFF(mm, @a, @b);\r\n<\/pre>\n
      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.<\/p>\n
      The problem is not with the  DATEDIFF<\/b> 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.<\/p>\n
      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:<\/p>\n
      DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\r\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\r\nSELECT DATEDIFF(ss, @a, @b)\/60.0000;\r\n<\/pre>\n
      Now the  SELECT<\/code><\/b> statement returns 0.0166666<\/code><\/b>, rather than  1<\/code><\/b>, which is much closer to the truth.<\/p>\n
      Failure #8: Being careless with your search conditions<\/h2>\n
      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.<\/p>\n
      To demonstrate why this might be an issue, let’s first create a temporary table and populate it with several rows that include  DATETIME2<\/b> data:<\/p>\n
      CREATE TABLE #a (ColA INT, ColB DATETIME2(3));\r\nINSERT INTO #a VALUES\r\n\u00a0 (101, '2015-05-06 22:43:55.123'),\r\n\u00a0 (102, '2015-05-06 23:59:59.997'),\r\n\u00a0 (103, '2015-05-07 00:00:00.000'),\r\n\u00a0 (104, '2015-05-07 17:33:36.321'),\r\n\u00a0 (105, '2015-05-08 00:00:00.000'),\r\n\u00a0 (106, '2015-05-08 10:18:12.987');\r\n<\/pre>\n
      Not let’s try to select the rows for May 7, 2015:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE ColB = '2015-05-07';\r\n<\/pre>\n
      As the following results show, our query returns only one row, when we want to be seeing two:<\/p>\n\n\n\n\n
      \n
      ColA<\/b><\/p>\n<\/td>\n
      		\n
      ColB<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      103<\/p>\n<\/td>\n
      		\n
      2015-05-07 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The challenge with trying to retrieve the data in this way is that the DATETIME2<\/code><\/b> 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<\/code><\/b> clause above will filter out all rows except those with a matching date and with midnight for the time.<\/p>\n
      One way to get around this is to convert the column data to match the date-only value:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE CONVERT(CHAR(8), ColB, 112) = '20150507';\r\n<\/pre>\n
      Now the  SELECT<\/code><\/b> statement returns the results we want:<\/p>\n\n\n\n\n\n
      \n
      ColA<\/b><\/p>\n<\/td>\n
      		\n
      ColB<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      103<\/p>\n<\/td>\n
      		\n
      2015-05-07 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n
      \n
      104<\/p>\n<\/td>\n
      		\n
      2015-05-07 17:33:36.321<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      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<\/b> operator to retrieve the day’s data:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE ColB BETWEEN '2015-05-06' AND '2015-05-08';\r\n<\/pre>\n
      This time, however, we get more rows that we bargain for:<\/p>\n\n\n\n\n\n\n\n\n
      \n
      ColA<\/b><\/p>\n<\/td>\n
      		\n
      ColB<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      101<\/p>\n<\/td>\n
      		\n
      2015-05-06 22:43:55.123<\/p>\n<\/td>\n<\/tr>\n
      \n
      102<\/p>\n<\/td>\n
      		\n
      2015-05-06 23:59:59.997<\/p>\n<\/td>\n<\/tr>\n
      \n
      103<\/p>\n<\/td>\n
      		\n
      2015-05-07 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n
      \n
      104<\/p>\n<\/td>\n
      		\n
      2015-05-07 17:33:36.321<\/p>\n<\/td>\n<\/tr>\n
      \n
      105<\/p>\n<\/td>\n
      		\n
      2015-05-08 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The challenge here is that the  BETWEEN<\/b> 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<\/b> with only the date we want to include:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE ColB BETWEEN '2015-05-07' AND '2015-05-07';\r\n<\/pre>\n
      Once again the SELECT<\/code><\/b> statement is back to returning only one row:<\/p>\n\n\n\n\n
      \n
      ColA<\/b><\/p>\n<\/td>\n
      		\n
      ColB<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      103<\/p>\n<\/td>\n
      		\n
      2015-05-07 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The problem this time is similar to using  WHERE<\/b>  ColB<\/b>  =<\/b> '2015-05-07'<\/code><\/b>. The BETWEEN<\/code><\/b> operator is basing its calculations on the entire value, including the time, so our WHERE<\/code><\/b> clause essentially looks like the following:<\/p>\n
      WHERE ColB BETWEEN '2015-05-07 00:00:00.000' AND '2015-05-07 00:00:00.000';\r\n\t<\/pre>\n
      Of course, this will return only the same one row. However, we can address this issue by being more precise with our end date:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59:999';\r\n<\/pre>\n
      Now the  SELECT<\/code><\/b> statement returns the results we want:<\/p>\n\n\n\n\n\n
      \n
      ColA<\/b><\/p>\n<\/td>\n
      		\n
      ColB<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      103<\/p>\n<\/td>\n
      		\n
      2015-05-07 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n
      \n
      104<\/p>\n<\/td>\n
      		\n
      2015-05-07 17:33:36.321<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Although this approach works fine for the  DATETIME2<\/b> data type, we can simplify the code by instead using equality operators to define our search condition:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE ColB >= '2015-05-07' AND colB < '2015-05-08';\r\n<\/pre>\n
      The  SELECT<\/code><\/b> 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<\/b> data type. Let me demonstrate. Suppose we had originally defined ColB<\/code><\/b> with the DATETIME<\/code><\/b> data type and populated the table accordingly:<\/p>\n
      CREATE TABLE #a (ColA INT, ColB DATETIME);\r\nINSERT INTO #a VALUES\r\n\u00a0 (101, '2015-05-06 22:43:55.123'),\r\n\u00a0 (102, '2015-05-06 23:59:59.997'),\r\n\u00a0 (103, '2015-05-07 00:00:00.000'),\r\n\u00a0 (104, '2015-05-07 17:33:36.321'),\r\n\u00a0 (105, '2015-05-08 00:00:00.000'),\r\n\u00a0 (106, '2015-05-08 10:18:12.987');\r\n<\/pre>\n
      Now let’s use the BETWEEN<\/code><\/b> operator with our precise data range to retrieve the data:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59:999';\r\n<\/pre>\n
      This time, our SELECT<\/code><\/b> statement returns three rows:<\/p>\n\n\n\n\n\n\n
      \n
      ColA<\/b><\/p>\n<\/td>\n
      		\n
      ColB<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      103<\/p>\n<\/td>\n
      		\n
      2015-05-07 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n
      \n
      104<\/p>\n<\/td>\n
      		\n
      2015-05-07 17:33:36.320<\/p>\n<\/td>\n<\/tr>\n
      \n
      105<\/p>\n<\/td>\n
      		\n
      2015-05-08 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Because we’re comparing our  BETWEEN<\/b> dates with a  DATETIME<\/b> value, the database engine rounds the values we enter to conform to the DATATIME<\/code><\/b> 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:<\/p>\n
      SELECT ColA, ColB FROM #a\r\nWHERE ColB >= '2015-05-07' AND colB < '2015-05-08';\r\n<\/pre>\n
      Now the  SELECT<\/code><\/b> statement returns the expected two rows:<\/p>\n\n\n\n\n\n
      \n
      ColA<\/b><\/p>\n<\/td>\n
      		\n
      ColB<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      103<\/p>\n<\/td>\n
      		\n
      2015-05-07 00:00:00.000<\/p>\n<\/td>\n<\/tr>\n
      \n
      104<\/p>\n<\/td>\n
      		\n
      2015-05-07 17:33:36.320<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      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.<\/p>\n
      Failure #9: Forgetting about data type year limitations<\/h2>\n
      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:<\/p>\n
      DECLARE @a DATETIME2 = '1623-01-01',\r\n\u00a0 @b DATETIME = NULL;\r\nSET @b = @a;\r\nSELECT @b;\r\n<\/pre>\n
      We’re trying to convert a DATETIME2<\/code><\/b> value for the year 1623 to a DATETIME<\/code><\/b> value. Unfortunately, the DATETIME<\/code><\/b> 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:<\/p>\n
      Msg 242, Level 16, State 3, Line 792\r\nThe conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.\r\n<\/pre>\n
      The message should be self-evident. The  DATETIME<\/b> data type has no interest in the year 1623. The SMALLDATETIME<\/code><\/b> data type is even more limited:<\/p>\n
      DECLARE @a DATETIME = '1823-01-01',\r\n\u00a0 @b SMALLDATETIME = NULL;\r\nSET @b = @a;\r\nSELECT @b;\r\n<\/pre>\n
      The  SELECT<\/code><\/b> statement will once again return an out-of-range error because the  SMALLDATETIME<\/b> 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.<\/p>\n
      Failure #10: Not taking full advantage of SQL Server’s date\/time functions<\/h2>\n
      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<\/b> or  GETUTCDATE<\/b>.<\/p>\n
      Let’s look at some of the date\/time functions in action:<\/p>\n
      SELECT GETDATE();\r\nSELECT SYSDATETIME();\r\nSELECT GETUTCDATE();\r\nSELECT SYSUTCDATETIME();\r\nSELECT SYSDATETIMEOFFSET();\r\n<\/pre>\n
      As you can see in the following results, we have a variety of options from which to choose:<\/p>\n
      2015-05-14 10:28:21.700\r\n2015-05-14 10:28:21.7030000\r\n2015-05-14 17:28:21.700\r\n2015-05-14 17:28:21.7030000\r\n2015-05-14 10:28:21.7030000 -07:00\r\n<\/pre>\n
      The  SYSDATETIME<\/code><\/b> function returns the current date and time as a  DATETIME2<\/b> value. The  SYSUTCDATETIME<\/b> function returns the same data, but as a UTC value. The SYSDATETIMEOFFSET<\/code><\/b> function returns the current date and time as a  DATETIMEOFFSET<\/b> value, which means we also get the offset amount.<\/p>\n
      SQL Server 2008 also included enhancements to the DATENAME<\/b> and  DATEPART<\/b> 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<\/code><\/b> function:<\/p>\n
      DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';\r\nSELECT DATENAME(mcs, @a) AS Microseconds,\r\n\u00a0 DATENAME(ns, @a) AS Nanoseconds,\r\n\u00a0 DATENAME(tz, @a) AS TimezoneOffset;\r\n<\/pre>\n
      And here are our results:<\/p>\n\n\n\n\n
      \n
       Microseconds<\/b><\/p>\n<\/td>\n
      		\n
       Nanoseconds<\/b><\/p>\n<\/td>\n
      		\n
       TimezoneOffset<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      904672<\/p>\n<\/td>\n
      		\n
      904672200<\/p>\n<\/td>\n
      		\n
      -07:00<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The  DATEPART<\/code><\/b> function works pretty much the same way:<\/p>\n
      DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';\r\nSELECT DATEPART(mcs, @a) AS Microseconds,\r\n\u00a0 DATEPART(ns, @a) AS Nanoseconds,\r\n\u00a0 DATEPART(tz, @a) AS TimezoneOffset;\r\n<\/pre>\n
      Here are the results this time around:<\/p>\n\n\n\n\n
      \n
       Microseconds<\/b><\/p>\n<\/td>\n
      		\n
       Nanoseconds<\/b><\/p>\n<\/td>\n
      		\n
       TimezoneOffset<\/b><\/p>\n<\/td>\n<\/tr>\n
      \n
      904672<\/p>\n<\/td>\n
      		\n
      904672200<\/p>\n<\/td>\n
      		\n
      -420<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      The results are fairly similar to  DATENAME<\/b>, except for the offset value, which is in minutes, rather than hours.<\/p>\n
      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.<\/p>\n
       <\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
      One of the times that you need things to go right is when you are doing analysis and reporting. This is generally based on time and date. A sure-fire way of getting managers upset is to get the figures horribly wrong by messing up the way that you handle datetime values in SQL Server. In the interests of peace, harmony and a long career in BI, Robert Sheldon outlines some of the worst mistakes you can make when using SQL Server dates.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4178,5994,4150,5134,4151,4252],"coauthors":[6779],"class_list":["post-2002","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-bi","tag-horribly","tag-sql","tag-sql-prompt","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2002","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2002"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2002\/revisions"}],"predecessor-version":[{"id":83117,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2002\/revisions\/83117"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2002"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2002"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2002"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2002"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}