The Datetime Dilemma

Storing date and time data can be a challenge in SQL Server because the only mechanism for storing this kind of data doesn't let you separate the date—day/month/year—from the time. SQL Server developers might hope for a separation between date and time in future SQL Server releases, but for now, you have to use one data type (either datetime or smalldatetime) when you store date- and time-related data.

Because both data types include date and time, using these types is often confusing. In fact, for good reason, this programming topic appears frequently on the newsgroups and in public forums. Because programmers have different levels of experience and because client-application interfaces (e.g., ODBC, OLE DB, ADO .NET) incorporate certain default behaviors while allowing additional customizable behaviors, bad data is often stored or returned. Moreover, multiple options for entering, manipulating, and displaying date and time data add to the complexity. Let's dissect the topic of storing date and time data by reviewing storage basics, data-entry options, and date and time data display formats. To test the syntax for this topic, you can walk through the DateTimeDataExamples.sql commented script file, which you can find at the top of the page.

Storage Basics

As I mentioned above, SQL Server supports two datetime data types: datetime and smalldatetime. Both data types store date and time, but their storage size and precision vary. At 8 bytes, the datetime data type's capacity is larger than that of smalldatetime, whose storage capacity is only 4 bytes. This variation in capacity determines the difference in their range of possible values and their time precision. Datetime allows dates between January 1, 1753, and December 31, 9999, whereas smalldatetime allows dates only between January 1, 1900, and June 6, 2079. Also, a datetime data type's time is precise to 3.33 milliseconds, but the smalldatetime data type rounds time to the nearest minute. Both data types can potentially lose precision, even if you input the time to the millisecond. For a discussion of precision loss and sample code to show you how these data types work, see the sidebar "Just in Time."

Always choose the best data type for the job. For example, if you need to store precise sales dates in a high volume of sales records or if you want to enter creation dates for artifacts from the 19th century, you need the datetime data type. But if you're storing hire dates for a company that's only 12 years old, you'll probably want to use smalldatetime, which claims less disk space and might achieve more efficient cache utilization. Typically, if rows are compact, more rows will fit on a page. When a table is compact (i.e., it uses fewer pages in both storage and memory), it more effectively uses cache because it needs fewer pages in memory to store the necessary information.

Data Input

Although datetime and smalldatetime data types include both date and time components, you don't need to supply both components when you input data. This flexibility is a mixed blessing, however. Because SQL Server always stores both a date and a time, SQL Server assigns a default value when you don't supply both parts of date and time data. If possible, supply both components to ensure accuracy. You can provide the data in many possible formats, and you can modify the default behavior for data entry and format. Let's look at the settings that control those choices.

First, you should learn how your application environment settings are controlled. When a client connects to the server, a session is established. The session settings are determined by the tools the client uses (such as the ISQL and OSQL command-line utilities, Query Analyzer, or a custom application) and the SET options that a developer or user has modified. The best way to find out which settings your client has chosen is to execute DBCC USEROPTIONS to return the list of options and their current settings. For example, one option might be dateformat, whose setting is the mdy value.

The dateformat's mdy setting shows that you should enter your data with month first, day second, and year third. Note that this setting doesn't control date and time data display; it only details the order in which you input the values. This setting also doesn't tell you to designate the year as two or four digits; it just tells you that the year component has to appear last. Use Listing 1 to review several INSERT statements for dates in which no time component has been specified. A SELECT statement follows the inserts so that you can review the data as it was input. In the output, notice that the query always returns the century value, regardless of whether you supply a two- or four-digit year. Take a closer look at the INSERT and SELECT statements: Is the century value what you expected? First, the INSERT statements used different separators to split up the day, month, and year components—a slash (/), a dash (-), and a period (.), all of which are valid. Second, I specified all the years as two-digit years—01, 49, and 72. However, when the query selected the data, it returned four-digit years for all three rows, showing 2001, 2049, and 1972, respectively. SQL Server assumed the century from a server setting.

When SQL Server stores dates in a database, the dates are always complete and include the century. SQL Server automatically converts a two-digit year to 19nn when the year is 50 or higher and 20nn when the year is under 50. However, beginning with SQL Server 7.0, you can change this default behavior by using sp_configure or Enterprise Manager. To modify the default through Enterprise Manager, right-click your server, choose Properties to open the Server Properties dialog box, then click the Server Settings tab. At the bottom of this dialog box, you can change two digit year cutoff to something other than 2049. For example, when you change this value to 2099, the low value automatically will be 2000—meaning that all two-digit years entered will become 20nn values. Conversely, when you change the high value to 1999, all two-digit years entered will become 19nn values. However, some developers, users, and applications might rely on the default behavior, so to safeguard accuracy, I recommend against changing the default. Above all, the best course of action is to supply a four-digit year every time you enter a date.

The dates I've discussed so far have been supplied as numerical values only; however, SQL Server also supports inputting a text-formatted month in two formats: the month's first three characters or the completely spelled-out word. Sep and September are both valid. When you specify a two-digit day, a text-formatted month, and a four-digit year, SQL Server always identifies the components correctly because of the form in which you supply the components. However, if you supply a two-digit day, a text-formatted month, and a two-digit year, SQL Server assumes that the year comes after the day—regardless of the dateformat setting. Listing 2 shows a few unusual examples of this observable fact, but requesting consistent formatting from client applications, developers, and users is the way to handle SQL Server's overly flexible options for inputting date and time data.

And what about the time component? In Listing 1 and 2's inserts, I supplied dates with no times. But SQL Server implicitly supplied a time for all rows, as Table 1 shows. The default time is always midnight, which the default display output shows as 00:00:00. You can also enter a time manually, typically by using a colon (:) to separate each time section. You can use a 12-hour or 24-hour time format; however, when you use the 12-hour clock to enter a pm value, you must state "pm," as the inserts in Listing 3 show. The time that the SELECT statement returned is accurate, but check the date component. If you supply a time with no date, SQL Server automatically supplies a date of January 1, 1900, for both the datetime and smalldatetime data types.

Datetime Data Display

When you retrieve datetime data, you see the default output format that the client defines. For consistency, I used Query Analyzer to display and test this column's code snippets. If you use a tool such as the isql.exe command-line utility to review these code snippets, the date values will be the same dates, but the output will appear in a different format. If you're interested in seeing the default format, use your tool of choice to do a SELECT * from TestDates. However, my recommendation for displaying date and time data is to take control of the display. Table 1's result set shows Query Analyzer's default display. This returned data shows the date format as yyyy-mm-dd hh:mi:ss.mmm for the datetime data type and yyyy-mm-dd hh:mi:ss for smalldatetime, even though smalldatetime doesn't keep track of seconds. In fact, regardless of the defaults, your best choice for returning date and time data is to explicitly control and format the display.

Typically, SQL Server developers use the CONVERT() function to change data from one data type to another, but you can use CONVERT() to control date and time data as well. To change the datetime data style, you need to change the data type to a string of the same length as the desired output. For example, if you're looking for a date alone in the mm/dd/yyyy format, the total number of characters you need is 10 (two for month, two for day, four for year, and two for the forward slashes). If you prefer a two-digit year, you need eight characters. To use CONVERT() to change the style of your data, you begin by choosing your format. The best place to review the formats available is in SQL Server Books Online (BOL). Under the CONVERT topic is a table that shows all the datetime data- conversion styles that SQL Server supports. Browse through the list and find the style that best fits your business needs. Among the styles are some that don't include dates, and others that lack times. The CONVERT() syntax takes the following order:

CONVERT(datatypeTO, expression, style)

If you use the getdate() system function to display the current date in the 10-character format, the data type you convert to is a char(10) and the expression you convert from is the getdate() expression. To set the query's style, you need to use the appropriate style number for the third parameter. In BOL's CONVERT table, the style is number 1 for mm/dd/yy and 101 for mm/dd/yyyy. Typically, styles of 100 and above display a four-digit year, whereas styles below 100 display a two-digit year. When you pick a style for a two-digit year, you can add 100 to it to return a four-digit year. The following query displays the getdate() expression in mm/dd/yyyy format:

SELECT CONVERT(char(10), getdate(), 101)

Notice that the query's output doesn't produce a column heading, a result that can be especially problematic for datetime values. For example, your international users might assume day and month in reverse order from your US users and subsequently produce bad data analysis. To avoid these problems with date and time format, I recommend that you always use a column header and state the date format in the header, as in the following line of code:

SELECT CONVERT(char(10), getdate(), 101) AS 'MM/DD/YY'

Storage Made Easy

At first, dealing with both date and time might be overwhelming when you're interested in storing only one of those datetime components. However, after you set out to be consistent with the data access and perform a few conversions, you'll be well on your way to storing and displaying datetime data that's accurate, properly formatted, and easily understood. After you've mastered the basics, you might want to perform more complex formatting and even create views to simplify user access to your date- and time-related data. In the next T-SQL Tutor column, I'll describe the built-in datetime functions and show you how to properly manipulate date and time data as well as perform date and time mathematics.