{"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":"2026-04-30T11:25:26","modified_gmt":"2026-04-30T11:25: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":"SQL Server Dates and Times: Ten Common Failures from DATETIME to DATEDIFF"},"content":{"rendered":"
\n\nSQL Server date and time handling trips up even experienced developers, and the consequences – wrong dates, missed rows, silently corrupted data – often surface only in production. <\/strong>\n\nThis article catalogues ten common failure modes, as listed below. <\/strong>Each failure is shown with a working example of the wrong behavior and the corrected approach.<\/strong>\n\n<\/div>\n
\n
    \n \t
  1. Assuming SQL Server stores date\/time values as formatted strings rather than binary<\/li>\n \t
  2. Hard-coding date formats that don’t survive regional settings<\/li>\n \t
  3. Not handling DATEFORMAT locale differences in string-to-date conversions<\/li>\n \t
  4. Treating DATETIME2 as just DATETIME with extra precision<\/li>\n \t
  5. Ignoring how SQL Server rounds DATETIME (to 3.33ms intervals)<\/li>\n \t
  6. Removing the time portion of a date poorly (CAST to DATE vs DATEADD vs CONVERT)<\/li>\n \t
  7. Misunderstanding DATEDIFF (which counts boundary crossings, not elapsed time)<\/li>\n \t
  8. Writing search conditions that exclude edge-case rows (e.g., WHERE col = ‘2024-01-15’ missing any rows with a time component);\u00a0 (<\/li>\n \t
  9. Forgetting year-range limits on specific types (DATETIME minimum is 1753)<\/li>\n \t
  10. Not using SQL Server’s built-in date functions (DATEFROMPARTS, EOMONTH, ISDATE)<\/li>\n<\/ol>\n

    #1: Assuming SQL Server stores date\/time values as formatted strings rather than binary<\/h2>\nMany 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.)\n\nBeginning 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.\n\nLet’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.\n\nThe 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.\n\nThis 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:\n
    DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\nSELECT @a;\n<\/pre>\nAs we’ve come to expect, the  SELECT<\/b> statement returns the value in the following format:\n
    2015-05-07 10:05:23.187\n<\/pre>\nTo get at the actual value, we must first convert it to the VARBINARY<\/code><\/b> type:\n
    DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\nSELECT CONVERT(VARBINARY(8), @a);\n<\/pre>\nNot surprisingly, our results now look quite different:\n
    0x0000A49100A6463C\n<\/pre>\nBecause 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:\n
    DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\nSELECT SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4);\n<\/pre>\nNow the SELECT<\/code><\/b> statement returns only the bytes that represent the date integer:\n
    0x0000A491\n<\/pre>\nWe 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:\n
    DECLARE @a DATETIME = '2015-05-07 10:05:23.187'\nSELECT \n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\n<\/pre>\nThe following table shows the results returned by the SELECT<\/b> statement:\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>\nWhat 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:\n
    DECLARE @a DATETIME = '1827-12-03 22:15:11.297'\nSELECT \n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\n<\/pre>\nThis 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:\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>\nNow let’s set the date and time to the default (day 0):\n
    DECLARE @a DATETIME = '1900-01-01 00:00:00.000'\nSELECT \n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\n<\/pre>\nAs expected, the VARBINARY<\/code><\/b> and INT<\/code><\/b> values all zero out:\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>\nJust to make sure this is all clear, here’s one more DATETIME<\/b> example, one day and one second after the default:\n
    DECLARE @a DATETIME = '1900-01-02 00:00:01.000'\nSELECT \n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS DateBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 1, 4) AS INT) AS DateInt,\n\u00a0\u00a0 SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS TimeBinary,\n\u00a0\u00a0 CAST(SUBSTRING(CONVERT(VARBINARY(8), @a), 5, 4) AS INT) AS TimeInt;\n<\/pre>\nAt the point, the results should be fairly predictable. We have a date integer of 1 and a time integer of 300:\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>\nThis 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):\n
    DECLARE @b DATETIME2 = '2015-05-07 10:05:23.1872436'\nSELECT CONVERT(VARBINARY(10), @b);\n<\/pre>\nThis time our results look a bit different from the DATETIME<\/b> value:\n
    0x07B4854E9254EC390B\n<\/pre>\nFor 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:\n
    DECLARE @b DATE = '2015-05-07 10:05:23.1872436'\nSELECT CONVERT(VARBINARY(10), @b);\n<\/pre>\nOur results match those of the date portion returned in the previous example:\n
    0xEC390B\n<\/pre>\nAnd it works the same way for the  TIME<\/b> data type:\n
    DECLARE @b TIME = '2015-05-07 10:05:23.1872436'\nSELECT CONVERT(VARBINARY(10), @b);\n<\/pre>\nThe results match those of the time portion returned by the DATETIME2<\/code><\/b> example:\n
    0x07B4854E9254\n<\/pre>\nNow 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:\n
    DECLARE @b DATETIME2(4) = '2015-05-07 00:00:00.0001'\nSELECT CONVERT(VARBINARY(10), @b);\n<\/pre>\nThe SELECT<\/code><\/b> statement returns the following results:\n
    0x0401000000EC390B\n<\/pre>\nNotice 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.\n\nThe 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.\n
    #2: Hard-coding date formats that don’t survive regional settings<\/h2>\nT-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>.\n\nTo 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>:\n
    SET LANGUAGE british;\nDECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';\nSELECT CAST(@a AS DATETIME);\n<\/pre>\nAs expected, the SELECT<\/code><\/b> statement returns the following results:\n
    2015-05-19 10:11:12.000\n<\/pre>\nNow let’s set the language to US English and then try to convert the value:\n
    SET LANGUAGE us_english;\nDECLARE @a VARCHAR(20) = '19-05-2015 10:11:12';\nSELECT CAST(@a AS DATETIME);\n<\/pre>\nThis time the database engine baulks and returns the following error:\n
    Msg 242, Level 16, State 3, Line 730\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\n<\/pre>\nThe 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:\n
    SET LANGUAGE us_english;\nDECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';\nSELECT CAST(@a AS DATETIME);\n<\/pre>\nNow 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.\n\nOf 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:\n
    SET LANGUAGE british;\nDECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';\nSELECT CAST(@a AS DATETIME);\n<\/pre>\nThe 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:\n
    Msg 242, Level 16, State 3, Line 204\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\n<\/pre>\nBut look at what happens if we stick with the same language and same format, but this time convert the data to  DATETIME2<\/b>:\n
    SET LANGUAGE british;\nDECLARE @a VARCHAR(20) = '2015-05-19 10:11:12';\nSELECT CAST(@a AS DATETIME2);\n<\/pre>\nThe SELECT<\/code><\/b> statement now converts the date with no problem and returns the following results:\n
    2015-05-19 10:11:12.0000000\n<\/pre>\nIt 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>.\n\nIf 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:\n
    SET LANGUAGE british;\nDECLARE @a VARCHAR(20) = '2015-05-19T10:11:12';\nSELECT CAST(@a AS DATETIME);\n\n<\/pre>\nThis 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.\n
    #3: Not handling DATEFORMAT locale differences in string-to-date conversions<\/h2>\nOur 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.\n\nOne 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.\n\nIn 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.\n\nEven 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).\n\nFortunately, 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:\n
    DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();\nSELECT @pdt;\n<\/pre>\nThe 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:\n
    2015-05-08 17:57:23.5350000 -07:00\n<\/pre>\nIn 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:\n
    DECLARE @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();\nSELECT DATENAME(tzoffset, @pdt);\n<\/pre>\nAs expected, the SELECT<\/code><\/b> statement returns only the difference from UTC:\n
    -07:00\n<\/pre>\nWe can further demonstrate how the  DATETIMEOFFSET<\/b> data type works by comparing it to its UTC counterpart:\n
    DECLARE @utc DATETIMEOFFSET = SYSUTCDATETIME(),\n\u00a0 @pdt DATETIMEOFFSET = SYSDATETIMEOFFSET();\nSELECT @utc AS UTC, @pdt AS PDT;\n<\/pre>\nAs the following results show, the UTC date and time are seven hours ahead of the Pacific date and time.\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>\nSince 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:\n
    DECLARE @a DATETIMEOFFSET = '2015-05-08 17:57:53.3390000 -07:00';\nSELECT SWITCHOFFSET(@a, '-05:00');\n<\/pre>\nIn 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:\n
    2015-05-08 19:57:53.3390000 -05:00\n<\/pre>\nClearly, 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).\n\nLet’s look at what happens when we compare time zones between Melbourne and Seattle on April 1, 2015:\n
    DECLARE @mel DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 +11:00',\n\u00a0 @sea DATETIMEOFFSET = '2015-04-01 17:57:53.3390000 -07:00';\nSELECT DATEDIFF(hh, @mel, @sea);\n<\/pre>\nBoth 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:\n
    DECLARE @mel DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 +11:00',\n\u00a0 @sea DATETIMEOFFSET = '2015-05-01 17:57:53.3390000 -07:00';\nSELECT DATEDIFF(hh, @mel, @sea);\n<\/pre>\nWe 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.\n\nAlthough 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.\n\nAdd 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.\n\nWhat 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.\n
    #4: Treating DATETIME2 as just DATETIME with extra precision<\/h2>\nAlthough 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.\n\nLet’s start by looking at the two in action:\n
    DECLARE @a DATETIME2 = '2015-05-12 09:47:12.5556789',\n\u00a0 @b DATETIME = '2015-05-12 09:47:12.555';\nSELECT @a AS DateTime2Type, @b AS DateTimeType;\n<\/pre>\nThe 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.\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>\nThe 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.\n\nSo 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:\n
    DECLARE @a DATETIME2(3) = '2015-05-12 09:47:12.5556789',\n\u00a0 @b DATETIME = '2015-05-12 09:47:12.555';\nSELECT @a AS DateTime2Type, @b AS DateTimeType;\n<\/pre>\nAs you can see in the following results, the  DATETIME2<\/b> value now includes only three decimal places, just like the DATETIME<\/code><\/b> value.\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>\nOnce 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.\n\nIn 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.\n\nThe DATETIME2<\/code><\/b> data type also has the advantage of being able to remove the decimal places altogether:\n
    DECLARE @a DATETIME2(0) = '2015-05-12 09:47:12.5556789',\n\u00a0 @b DATETIME = '2015-05-12 09:47:12.555';\nSELECT @a AS DateTime2Type, @b AS DateTimeType;\n<\/pre>\nBeing able to zero out the decimals is a nice feature when you don’t need the microseconds. Now our results are much cleaner:\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>\nAnother 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.)\n\nAlso 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\n
    DECLARE @a DATE = '2015-05-12 09:47:12.5556789',\n\u00a0 @b TIME = '2015-05-12 09:47:12.5556789';\nSELECT @a AS DateType, @b AS TimeType;\n<\/pre>\nAs 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:\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>\nLegacy 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.\n
    #5: Ignoring how SQL Server rounds DATETIME (to 3.33ms intervals)<\/h2>\nIn 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:\n
    DECLARE @a DATETIME2 = '2015-05-12 23:32:12.1234567',\n\u00a0 @b DATETIME2 = '2015-05-12 23:32:12.123456789',\n\u00a0 @c DATETIME2 = '2015-05-12 23:59:59.999999999';\nSELECT '2015-05-12 23:32:12.1234567' AS OrigValue, @a AS StoredValue\nUNION ALL\nSELECT '2015-05-12 23:32:12.123456789', @b\nUNION ALL\nSELECT '2015-05-12 23:59:59.999999999', @c;\n<\/pre>\nIn 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:\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>\nThe @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.\n\nNow lets look what happens with a few  DATETIME<\/b> values:\n
    DECLARE @a DATETIME = '2015-05-12 23:59:59.996',\n\u00a0 @b DATETIME = '2015-05-12 23:59:59.998',\n\u00a0 @c DATETIME = '2015-05-12 23:59:59.999';\nSELECT '2015-05-12 23:59:59.996' AS OrigValue, @a AS DatetimeValue\nUNION ALL\nSELECT '2015-05-12 23:59:59.998', @b\nUNION ALL\nSELECT '2015-05-12 23:59:59.999', @c;\n<\/pre>\nThe @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:\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>\nWhat’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.\n\nThe 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:\n
    DECLARE @a DATETIME2 = '2015-05-12 23:59:59.9986789';\nDECLARE @b DATETIME = @a;\nSELECT @a AS Datetime2Value, @b AS DatetimeValue;\n<\/pre>\nBecause the original precision exceeds what  DATETIME<\/b> can handle, more rounding occurs, once again jumping to the next day:\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>\nWe can run into even more confusing issues with the SMALLDATETIME<\/b> data type:\n
    DECLARE @a SMALLDATETIME = '2015-05-12 23:22:22',\n\u00a0 @b SMALLDATETIME = '2015-05-12 23:22:30',\n\u00a0 @c SMALLDATETIME = '2015-05-12 23:22:52',\n\u00a0 @d SMALLDATETIME = '2015-05-12 23:59:52';\nSELECT '2015-05-12 23:22:22' AS OrigValue, @a AS SmalldatetimeValue\nUNION ALL\nSELECT '2015-05-12 23:22:30', @b\nUNION ALL\nSELECT '2015-05-12 23:22:52', @c\nUNION ALL\nSELECT '2015-05-12 23:59:52', @d;\n<\/pre>\nThe SMALLDATETIME<\/code><\/b> is accurate only to the nearest minute, despite the fact that the returned values always show 00 seconds:\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>\nThe 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.\n\nNow look at what happens if we add fractional seconds to the mix:\n
    DECLARE @a SMALLDATETIME = '2015-05-12 23:59:29.999';\nSELECT @a;\n<\/pre>\nOnce again, we flip over to the next day:\n
    2015-05-13 00:00:00\n<\/pre>\nYou’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.\n
    #6: Removing the time portion of a date poorly (CAST to DATE vs DATEADD vs CONVERT)<\/h2>\nOften 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:\n
    DECLARE @a DATETIME2(3) = '2015-05-12 22:14:18.003';\nSELECT CAST(@a AS DATE);\n<\/pre>\nIn 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.\n
    2015-05-12\n<\/pre>\nWe can just as easily convert a  DATETIME<\/b> value to a  DATE<\/b> value and get the same results:\n
    DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\nSELECT CAST(@a AS DATE);\n<\/pre>\nIn fact, we can even convert our original value to the TIME<\/code><\/b> data type:\n
    DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\nSELECT CAST(@a AS TIME(3));\n<\/pre>\nAs expected, the SELECT<\/code><\/b> statement now returns only the time:\n
    22:14:18.003\n<\/pre>\nBefore 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.\n\nHowever, 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:\n
    DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\nSELECT CAST(CONVERT(CHAR(8), @a, 112) AS DATETIME);\n<\/pre>\nAs 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:\n
    2015-05-12 00:00:00.000\n<\/pre>\nAlthough 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:\n
    DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\nSELECT DATEADD(DAY, DATEDIFF(DAY, 0, @a), 0);\n<\/pre>\nWhat 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:\n
    2015-05-12 00:00:00.000\n<\/pre>\nIn 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.\n\nA similar approach is to provide a specific date from which to work, rather than use day 0:\n
    DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\nSELECT DATEADD(DAY, DATEDIFF(DAY, '20010101', @a), '20010101');\n<\/pre>\nThis 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:\n
    DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\nSELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20010101');\n<\/pre>\nAll we’ve done is change the  DAY<\/b> arguments to  MONTH<\/b> to get the following results:\n
    2015-05-01 00:00:00.000\n<\/pre>\nIf 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:\n
    DECLARE @a DATETIME = '2015-05-12 22:14:18.003';\nSELECT DATEADD(MONTH, DATEDIFF(MONTH, '20010101', @a), '20001231');\n<\/pre>\nNow the SELECT<\/code><\/b> statement returns the following results:\n
    2015-04-30 00:00:00.000\n<\/pre>\nEven 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.\n
    #7: Misunderstanding DATEDIFF (which counts boundary crossings, not elapsed time)<\/h2>\nWhile 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:\n
    DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\nSELECT DATEDIFF(mi, @a, @b);\n<\/pre>\nIn 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:\n
    DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\nSELECT DATEDIFF(hh, @a, @b);\n<\/pre>\nThis 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:\n
    DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\nSELECT DATEDIFF(mm, @a, @b);\n<\/pre>\nAgain, 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.\n\nThe 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.\n\nOne 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:\n
    DECLARE @a DATETIME2(3) = '2015-12-31 23:59:59.000',\n\u00a0 @b DATETIME2(3) = '2016-01-01 00:00:00.000';\nSELECT DATEDIFF(ss, @a, @b)\/60.0000;\n<\/pre>\nNow the SELECT<\/code><\/b> statement returns 0.0166666<\/code><\/b>, rather than 1<\/code><\/b>, which is much closer to the truth.\n
    #8: Writing search conditions that exclude edge-case rows (e.g., WHERE col = ‘2024-01-15’ missing any rows with a time component);\u00a0 (<\/h2>\nReferencing 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.\n\nTo 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:\n
    CREATE TABLE #a (ColA INT, ColB DATETIME2(3));\nINSERT INTO #a VALUES\n\u00a0 (101, '2015-05-06 22:43:55.123'),\n\u00a0 (102, '2015-05-06 23:59:59.997'),\n\u00a0 (103, '2015-05-07 00:00:00.000'),\n\u00a0 (104, '2015-05-07 17:33:36.321'),\n\u00a0 (105, '2015-05-08 00:00:00.000'),\n\u00a0 (106, '2015-05-08 10:18:12.987');\n<\/pre>\nNot let’s try to select the rows for May 7, 2015:\n
    SELECT ColA, ColB FROM #a\nWHERE ColB = '2015-05-07';\n<\/pre>\nAs the following results show, our query returns only one row, when we want to be seeing two:\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>\nThe 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.\n\nOne way to get around this is to convert the column data to match the date-only value:\n
    SELECT ColA, ColB FROM #a\nWHERE CONVERT(CHAR(8), ColB, 112) = '20150507';\n<\/pre>\nNow the SELECT<\/code><\/b> statement returns the results we want:\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>\nThe 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:\n
    SELECT ColA, ColB FROM #a\nWHERE ColB BETWEEN '2015-05-06' AND '2015-05-08';\n<\/pre>\nThis time, however, we get more rows that we bargain for:\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>\nThe 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:\n
    SELECT ColA, ColB FROM #a\nWHERE ColB BETWEEN '2015-05-07' AND '2015-05-07';\n<\/pre>\nOnce again the SELECT<\/code><\/b> statement is back to returning only one row:\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>\nThe 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:\n
    WHERE ColB BETWEEN '2015-05-07 00:00:00.000' AND '2015-05-07 00:00:00.000';\n<\/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\n
    SELECT ColA, ColB FROM #a\nWHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59:999';\n<\/pre>\nNow the SELECT<\/code><\/b> statement returns the results we want:\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>\nAlthough 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:\n
    SELECT ColA, ColB FROM #a\nWHERE ColB >= '2015-05-07' AND colB < '2015-05-08';\n<\/pre>\nThe 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:\n
    CREATE TABLE #a (ColA INT, ColB DATETIME);\nINSERT INTO #a VALUES\n\u00a0 (101, '2015-05-06 22:43:55.123'),\n\u00a0 (102, '2015-05-06 23:59:59.997'),\n\u00a0 (103, '2015-05-07 00:00:00.000'),\n\u00a0 (104, '2015-05-07 17:33:36.321'),\n\u00a0 (105, '2015-05-08 00:00:00.000'),\n\u00a0 (106, '2015-05-08 10:18:12.987');\n<\/pre>\nNow let’s use the BETWEEN<\/code><\/b> operator with our precise data range to retrieve the data:\n
    SELECT ColA, ColB FROM #a\nWHERE ColB BETWEEN '2015-05-07' AND '2015-05-07 23:59:59:999';\n<\/pre>\nThis time, our SELECT<\/code><\/b> statement returns three rows:\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>\nBecause 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:\n
    SELECT ColA, ColB FROM #a\nWHERE ColB >= '2015-05-07' AND colB < '2015-05-08';\n<\/pre>\nNow the SELECT<\/code><\/b> statement returns the expected two rows:\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>\nRegardless of the type of time\/date data types we’re working with, using equality operators to define our search condition remains the best approach.\n
    #9: Forgetting year-range limits on specific types (DATETIME minimum is 1753)<\/h2>\nThis 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:\n
    DECLARE @a DATETIME2 = '1623-01-01',\n\u00a0 @b DATETIME = NULL;\nSET @b = @a;\nSELECT @b;\n<\/pre>\nWe’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:\n
    Msg 242, Level 16, State 3, Line 792\nThe conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.\n<\/pre>\nThe 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:\n
    DECLARE @a DATETIME = '1823-01-01',\n\u00a0 @b SMALLDATETIME = NULL;\nSET @b = @a;\nSELECT @b;\n<\/pre>\nThe 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.\n
    #10: Not using SQL Server’s built-in date functions (DATEFROMPARTS, EOMONTH, ISDATE)<\/h2>\nSQL 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>.\n\nLet’s look at some of the date\/time functions in action:\n
    SELECT GETDATE();\nSELECT SYSDATETIME();\nSELECT GETUTCDATE();\nSELECT SYSUTCDATETIME();\nSELECT SYSDATETIMEOFFSET();\n<\/pre>\nAs you can see in the following results, we have a variety of options from which to choose:\n
    2015-05-14 10:28:21.700\n2015-05-14 10:28:21.7030000\n2015-05-14 17:28:21.700\n2015-05-14 17:28:21.7030000\n2015-05-14 10:28:21.7030000 -07:00\n<\/pre>\nThe 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.\n\nSQL 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:\n
    DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';\nSELECT DATENAME(mcs, @a) AS Microseconds,\n\u00a0 DATENAME(ns, @a) AS Nanoseconds,\n\u00a0 DATENAME(tz, @a) AS TimezoneOffset;\n<\/pre>\nAnd here are our results:\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>\nThe DATEPART<\/code><\/b> function works pretty much the same way:\n
    DECLARE @a DATETIMEOFFSET = '2015-05-12 19:14:38.9046722 -07:00';\nSELECT DATEPART(mcs, @a) AS Microseconds,\n\u00a0 DATEPART(ns, @a) AS Nanoseconds,\n\u00a0 DATEPART(tz, @a) AS TimezoneOffset;\n<\/pre>\nHere are the results this time around:\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>\nThe results are fairly similar to  DATENAME<\/b>, except for the offset value, which is in minutes, rather than hours.\n\nYou 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.\n\nFor more articles in this series, see below:\n\n
    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>\n\n\n<\/div>\n\n\n
      \n    
      FAQs<\/h2>\n\n                        
      1. What is the difference between DATETIME and DATETIME2 in SQL Server?<\/h3>\n            
      \n                
      DATETIME stores dates from 1753-01-01 to 9999-12-31 with 3.33-millisecond precision using 8 bytes. DATETIME2 stores dates from 0001-01-01 to 9999-12-31 with up to 100-nanosecond precision using 6-8 bytes depending on the specified scale. DATETIME2 is the modern, preferred type for new development: wider date range, better precision, ANSI-compliant, same or less storage. The DATETIME type is retained only for backward compatibility. Do not treat DATETIME2 as simply ‘DATETIME with extra precision’ – the rounding behaviour differs and the storage format is distinct.<\/p>\n            <\/div>\n                    
      2. Why does my SQL Server DATEDIFF return unexpected results?<\/h3>\n            
      \n                
      DATEDIFF counts the number of boundary crossings between two dates at the specified interval, not the elapsed time. DATEDIFF(DAY, ‘2024-01-01 23:59:59’, ‘2024-01-02 00:00:01’) returns 1 even though the elapsed time is 2 seconds – there’s one midnight boundary between the two values. Similarly, DATEDIFF(YEAR, ‘2023-12-31’, ‘2024-01-01’) returns 1 even though the elapsed time is 1 day. For elapsed-time calculations, use DATEDIFF with smaller intervals (seconds or milliseconds) and then divide, or use DATEADD + comparison logic.<\/p>\n            <\/div>\n                    
      3. How do I remove the time from a date in SQL Server?<\/h3>\n            
      \n                
      The cleanest approach is CAST(your_column AS DATE), which returns just the date portion. This works from SQL Server 2008 onwards when the DATE type was introduced. Legacy alternatives include CONVERT(DATE, your_column) (identical to CAST in effect), CONVERT(CHAR(8), your_column, 112) + CAST back (slower), or DATEADD(DAY, DATEDIFF(DAY, 0, your_column), 0) (truncates time while keeping DATETIME type). Avoid string-manipulation approaches (CONVERT(VARCHAR, column, 101)) – they produce locale-dependent output and can fail in search conditions. For new code on SQL Server 2008+, CAST to DATE is the canonical answer.<\/p>\n            <\/div>\n                    
      4. Why does my WHERE clause on a DATETIME column miss some rows in SQL Server?<\/h3>\n            
      \n                
      A common trap: WHERE OrderDate = ‘2024-01-15’ compares DATETIME values, and a DATETIME with any time component (for example, ‘2024-01-15 14:30:00’) will not equal ‘2024-01-15 00:00:00’. The query silently misses all rows with a non-midnight time. The correct pattern is a range check: WHERE OrderDate >= ‘2024-01-15’ AND OrderDate < ‘2024-01-16’. This captures all rows on the target date regardless of time component and remains SARGable (allows index seeks). Never wrap the column in CAST\/CONVERT\/DATEADD in a WHERE clause – that disables the index.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
      Ten common failures developers make with SQL Server date and time handling: DATETIME vs DATETIME2 precision, string-format assumptions, regional date formats, DATEDIFF boundary pitfalls, rounding surprises, removing time from dates correctly, careless search conditions, data type year limits, and not using SQL Server’s built-in date functions.…<\/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":16,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2002\/revisions"}],"predecessor-version":[{"id":110260,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2002\/revisions\/110260"}],"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}]}}