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.