Datetime Values in Access Projects

Learn how Access handles dates and times to extract results in the format you need

Dates and times are important in database applications that require elements such as hire dates, birth dates, and order data. Microsoft Access projects have new data types and functions and Access projects include formats for representing datetime values that are different from the formats in .mdb files. When you understand how Access projects express datetime values, you can format dates to appear the way your database application dictates. (For information about creating an Access project, see "The Power of Two: Office 2000 & SQL Server 7.0," August 1999.)

Datetime data type values in SQL Server 2000 work similarly to, but not exactly like, the date/time data type in Access database (.mdb) files. Access 2000 uses both sets of data types. Also, the way that datetime values in Access projects behave when you run them from Query Analyzer in SQL Server is different from the way they behave when you run them from Access 2000 stored procedure templates. Some aspects of the differences between datetime data types in Access database files and SQL Server are significant; these aspects can affect how efficiently you can manipulate datetime values when you transition from .mdb files to Access projects. The aspects I address in this article are data types for representing dates and times, methods of comparing converted datetime values, and a bug and its workaround for the CONVERT function in Access projects.

SQL Server has two data types for storing dates and times. The SQL Server datetime data type can hold dates ranging from January 1, 1753, through December 31, 9999, with accuracy to 3.33 milliseconds. SQL Server stores datetime data types as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, and the second 4 bytes store the time of day represented as the number of milliseconds after midnight. (For more information about SQL Server data types, see Michael D. Reilly, T-SQL for Starters, "Data-Type Details," August 1999.) SQL Server also uses the smalldatetime data type to store dates and times as two 2-byte integers. The first two bytes store the number of days after January 1, 1900, and the second 2 bytes store the number of minutes since midnight. Because SQL Server databases often have tables with many more rows than .mdb files do, using the smallest data type appropriate to the values in a SQL Server database column is especially important. The decreased size of the smalldatetime data type results in a reduced date range and time resolution—from January 1, 1900, through June 6, 2079, with precision to the nearest minute.

A table of values can help to clarify the differences between data types. The insert_six_datetime_values stored procedure in Listing 1, page 26, creates the table_of_dates table, which has three columns: one with an identity value, another with a datetime value, and the last with a smalldatetime value. Both the datetime and smalldatetime values have default values equal to the getdate function, which returns the system's current date and time. The identity column seeds the first row with a value of one and increases the value of each subsequent row by one with each insert.

The stored procedure's last section inserts default values into the table_of_dates table. WAITFOR statements appear between pairs of INSERT statements to create different times in the return set. When a stored procedure executes INSERT statements as in Listing 1, the procedure doesn't display a return set when you run it from an Access stored procedure template, even if the procedure attempts to create such a set. For this reason, the example in Listing 1 doesn't attempt to create a return set. Although the stored procedure template lets you type commands into it as Query Analyzer does, it isn't as full-featured as Query Analyzer. By invoking the SQL Server stored procedure from within a Visual Basic for Applications (VBA) procedure, your applications can display new datetime values that the INSERT statement creates. VBA can access data that the stored procedure templates don't show.

The code in Listing 2, page 26, is a VBA procedure for displaying values from the table_of_dates table. Choose Tools, Macro, Visual Basic Editor (VBE) from the Database window menu of an Access project to open the associated VBA project. The project will have the procedure from the listing already loaded into it. Then, invoke the View, Code command if a Code window isn't already available to accept the example code. You need a Command ADO object and a Recordset ADO object to display table values. The cmd1 Command object first runs the insert_six_datetime_values stored procedure. Then, the VBA procedure reuses cmd1 to run a SELECT statement that extracts values from the table_of_dates table. Because Command objects have no instructions about how to display results, the VBA procedure passes the values that the SELECT statement extracted to the rst1 Recordset object. A Do loop prints successive rows from the return set to the Immediate window in the VBE.

Using the CONVERT Function

Datetime values appear as character data types in return sets from SELECT statements. You can use the CONVERT function to determine the format of the results. When you designate a varchar data type format for a datetime value, you can use CONVERT style parameter settings. For example, a style value of 100 specifies a format truncated to the minute with time of day designated by the hour and an AM or PM suffix. Assigning a 108 style value to a CONVERT function sets the date portion of a CONVERT function's return value to January 1, 1900, and places zeroes in the millisecond units of the return value. A style value of 109 causes the CONVERT function to return the input date and time values formatted to the millisecond. Using 109 as a style value causes the CONVERT function to return a value that is identical to the result you'd get if you selected a datetime value with no CONVERT function. SQL Server Books Online (BOL) contains detailed documentation about CONVERT syntax and parameter settings.

Listing 3 contains a stored procedure that sets a datetime value to the current value of the getdate function before applying the CONVERT function three times to represent that value in different formats. These formats illustrate the return values you can achieve with style parameters of 100, 108, and 109.

Figure 1 shows the output from running Listing 3's stored procedure in an Access project. The first column, Raw Datetime, presents the value returned from selecting a datetime value without any formatting. This column displays values rounded to the nearest second. The Defaultms column also formats its datetime value to the nearest second.

However, the Seconds column truncates its value down to the second, so the value for the Seconds column is one second less than the Raw Datetime or Defaultms columns. So when you're comparing output from a CONVERT function that uses a style value of 108 with outputs from other style values, which round up instead of truncating, you can get different results.

The way a datetime value in a CONVERT function displays in an Access project stored procedure template is different from the way it displays in Query Analyzer. When you return the datetime values from a stored procedure template, the SELECT statement doesn't represent milliseconds as it does in Query Analyzer. Because of this distinction, you might need to develop a workaround for selected application requirements.

Figure 2 shows the return set from running an excerpt from Listing 3's T-SQL script (beginning with the line containing SET NOCOUNT ON) inside Query Analyzer. The returns in Figure 2 confirm that the 108 style setting for the Seconds column can yield a second value that precisely matches that of other format settings. You can get this result whenever the milliseconds value doesn't round up to the nearest second for other formats.

More significant, the Query Analyzer output shows a millisecond value for the unconverted (raw) datetime value in the first column and the Defaultms column with a style parameter of 109. This result confirms that the Access stored procedure template can return erroneous results for datetime values by excluding the milliseconds for times. By using the DATEPART function, though, you can create a workaround for this problem.

Using the DATEPART Function

Several built-in SQL Server functions that Access projects can use let you efficiently process dates and times. One of these functions—the DATEPART function—can represent with an integer any individual element of a datetime or smalldatetime value. This function works identically in Access project stored procedure templates and in Query Analyzer. Therefore, you can extract all the parts of a datetime value and reconstruct them in any format. This capability is useful for working around the inability of the Access project stored procedure template to return milliseconds in character format.

In its SELECT statement, the stored procedure in Listing 4 returns all the parts of a datetime value. The getdate function assigns a value to a local variable, @mydatetime, and a series of DATEPART functions extract different elements of the datetime value. Then the SELECT statement represents the datetime parts as the return values from the stored procedure.

Listing 5 (which you can download by entering InstantDoc ID 9026 at http://www.sqlmag.com and opening the 9026.zip file that accompanies this article) includes a pair of VBA procedures, the for_converted_times_2 subprocedure and the leading_zero function procedure, which work together to process the return values from the stored procedure in Listing 4. Initially, the for_converted_times_2 subprocedure uses a Recordset object to capture the return values from the stored procedure in Listing 4. Because the example needs to extract only a return set, it doesn't need a Command object as the previous VBA procedure did.

After creating a recordset with the return values from the stored procedure, the for_converted_times_2 procedure in Listing 5 converts the integer return values to strings (with leading zeroes, if appropriate). The leading_zero function procedure returns its value in string format with 0, 1, or 2 leading zeroes. The calling routine must designate both a value to convert and a maximum number of zeroes to apply.

The for_converted_times_2 subprocedure closes by constructing a datetime representation in the format of the Query Analyzer output that Figure 2 shows. Two string expressions specify the representation, then a Debug.Print statement prints that representation to the VBE environment's Immediate window. Because this method of decomposing, then reconstructing, datetime values is so flexible, you could just as easily construct any other format for representing the datetime value.