Just how to l k for Date and Time Values

by Bryan Syverson, composer of Murach’s SQL for SQL Server

Suppose you’re composing a question to locate all of the invoices that have been written on 6, 2003 january. You understand through the control totals that 122 invoices were written that day. But when this query is run by you

The outcome set is empty http://www.datingmentor.org/iamnaughty-review. What are you doing?

How dates and times are saved in SQL Server

Before you can effortlessly query date/time (or temporal) data, you must know one thing about how precisely date/time values are kept. SQL Server supports two date/time data types datetime and smalldatetime. The difference between the two is the number of storage used. Datetime makes use of 8 bytes of storage space, while smalldatetime uses just 4 bytes. For this reason, datetime can represent date/time values within a wider range sufficient reason for more precision than smalldatetime. These distinctions are summarized in the dining table below.

Both datetime and smalldatetime represent the date and time as a value that is add up to the true number of days in relationship to a base date. That base date is midnight on January 1, 1900 in SQL Server. The smalldatetime type can only represent dates from this base date on as you can see in the table. On the other hand, the datetime type also can represent dates which are before January 1, 1900. To accomplish this, it stores those values as negative figures.

To visualize exactly how date/time values are kept, you can think about them as consisting of two parts. The integer portion represents the number of entire times since 1, 1900 january. The fractional part represents the small fraction of a time that’s passed since midnight. For instance, the date/time value n n that is representing January 4, 1900 is stored as 3.5. In this case, 3 represents three full times since the beds base date and 0.5 represents half of a day between midnight and n n. To see this, submit the query that is following

Note The CAST function clearly changes the info variety of a value as specified. Therefore in this declaration, the inner CAST changes the string literal ‘1900-01-04 12 00’ to a value of data kind datetime. Then, the external CAST changes that datetime value up to a value of data type float. The final result is a floating-point representation associated with the datetime value that represents n n on January 4, 1900.

Up to now, so excellent. Nevertheless the conditions that crop up in querying date/time data are brought on by confusion over two facts that are fundamental aren’t so apparent. First, date/time information kinds are approximate numerics, perhaps not numerics that are exact. Second, date/time information types can’t store a date with no time or a time without having a date.

Date/Time values are approximate numerics

Datetime and smalldatetime are like the data that is floating-point, float and real, in that they are approximate numerics. Which means the worth retrieved from SQL Server can be distinctive from the worth that was originally saved. For instance, in the event that you store the phrase 10/3.0 in a column of information kind float, you’ll recover a value 3.3333330000000001. Although this is really a representation that is reasonable of thirds, it is not exact as it’s curved past the 6 th digit. In fact, in the event that you add three such values together, you get 9.9999990000000007, not 10. Of program, most programmers appreciate this being a error that is rounding. And it’s a persistent problem for all electronic computers, not merely those operating SQL Server. Still, you have to be aware of it as you code search conditions.

The value retrieved from SQL Server is exactly the value that was originally stored in contrast, when working with exact numeric data. For example, in the event that you shop 10/3.0 in a line of information kind int, it is saved as 3 and retrieved as 3. In this situation, SQL Server implicitly casts caused by the expression being a genuine value, 3.333333. Then, SQL Server implicitly casts 3.333333 as an integer because it’s being kept in a line of type int. Even though this continues to be an error that is rounding it occurs prior to the value is stored, never as due to the real limits of computer storage space. The error was introduced by using the wrong data type, not by the inherent limitation of the data type itself in other words. Since the system constantly comes back the exact same value as ended up being saved, the information type is exact.

Now, to observe this impacts date/time values, l k at the date and time value for 8 00AM on January 4, 1900. While you saw above, n n with this day is saved as 3.5, or halfway through the 4th day. In contrast, 8 00AM is one third of the way through the so its representation will be approximate day. To see this for yourself, submit the after question

You’ll get the result that is following

However if you distribute this query

you’ll receive the results that are following

As you can plainly see, these three values are typical quite close. In fact, they are near enough to be considered 8 00AM for many applications. However, in a search condition based on a solitary value, such as for instance

you would just match those rows in which the stored value exactly fits 3.3333333333333335. You’ll see getting for this later on in this specific article.

Dates without times and times without times

SQL Server does not provide data kinds for storing just the date or simply the full time. So if you store a date/time value without an time that is explicit the fractional part of the value is placed to zero. This represents midnight as 00 00 00. Similarly, if you shop a date/time value lacking any date that is explicit the integer percentage of the worthiness is defined to zero. This represents the date 1, 1900 january. To see this, submit the after question

which returns the following result

You query a date/time column depends on how the column has been designed and used whether you can ignore the date or the time component when.

The effect of database design on querying