Microsoft Year 2000 Readiness Disclosure & Resource Center

Building Year-2000 Compliant Applications with Visual Studio and Windows DNA

Performing Data Arithmetic: C++ Component

The AddByDays routine from Listing 9 is called whenever the user did not specify the exact due date for the transaction, but instead specified the transaction to become due after a fixed number of days have passed. In this case, we will compute the due date for the transaction by adding the fixed number of days to the Transaction Date.

We could do this with the VISUAL BASIC function DateAdd, which returns a date to which the number of the specific time interval has been added. For example, to specify a Due Date of 30 days after the Transaction Date, we would use DueDate = DateAdd("d",30,TransactionDate). Instead of using the built-in VISUAL BASIC date addition function, however, we will use a COM component that we wrote in C++ that does essentially the same thing. This is not necessarily a good design decision, but it allows us to illustrate the date handling functionality of C++ within the scope of our application.

The C++ component will run in the same MTS package as the VISUAL BASIC component. We will create an instance of the C++ component from within VISUAL BASIC by using the CreateObject function. The C++ component has two methods. DateDiffX functions similarly to Visual Basic's DateDiff function, except that it only returns the difference in days between two dates. DateAddX functions similarly to Visual Basic's DateAdd function, except that it only adds or subtracts days to a given date.

Refer to Listing 9 for the syntax for creating an instance of the COM components from Visual Basic, and calling the DateAddX function. Listing 13 displays the C++ DateAddX function.

STDMETHODIMP CDateMath::DateAddX(IN long Number, IN DATE Date, OUT DATE * NewDate)
{
*NewDate = Date + Number;
return S_OK;
}

Listing 13 - C++ component implementation of DateAdd.

The COLEDateTime class encapsulates the Date data type in C++, which is analogous to the VISUAL BASIC Date data type. It is designed to be used with the COleVariant class used in Automation. The COLEDateTime class expresses dates as the number of days since December 30, 1899. Therefore, the date December 31, 1899 would be expressed as 1. This makes the date arithmetic for the DateAddX function very simple – we can simply add the number of days (30) to the date using the (+) operator. Since the Date data type is expressed in days, the result is itself a date.

To calculate the difference between two dates, we will use similar logic. Again, since the Date data type is expressed in days, we can simply subtract date1 from date2 to get the number of days between the two days.

STDMETHODIMP CDateMath::DateDiffX(IN DATE DATE1, IN DATE Date2, OUT long * Difference)
{
*Difference = Date2 - Date1;
return S_OK;
}

Listing 14 - C++ component implementation of DateDiff.

Saving the data: ADO and SQL Server

Once the data is validated (and converted into the proper format if necessary), we are ready to save the information into the database using a stored procedure. The best way to ensure no loss of precision when passing date-related data into a stored procedure is to declare the stored procedure’s parameters as Transact-SQL’s DateTime data type.

The datetime data type is stored in 8 bytes (two 4-byte integers) -- 4 bytes for the number of days before or after the base date of January 1, 1900, and 4 bytes for the number of milliseconds after midnight. By using the datetime data type, the validity of the value passed to the stored procedure is automatically enforced. If an invalid date is passed as a parameter, Transact-SQL will trigger a run-time error. Transact-SQL also has a less precise smalldatetime data type stored in 4 bytes. The smalldatetime data type consists of 2 bytes for the number of days after January 1, 1900, and 2 bytes for the number of minutes past midnight. Data values for smalldatetime range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

When passing dates as parameters to a stored procedure using ActiveX Data Objects (ADO), no conversion is required between Visual Basic's Date data type, and the ADO Parameter of type adDBDate (Listing 9). Don't use Transact-SQL character data types for date-related data. Not only will this be less efficient than using the DateTime data type, it also introduces the possibility of invalid dates getting into the database at some time (2/29/1900 for example). By using the datetime data type, as we've seen in all of the other examples, we are afforded some built-in protection by the environment.

There is one limitation when using the Transact-SQL datetime data type with Visual Basic. The datetime data type is accurate to the millisecond, but Visual Basic's Date data type is not. Visual Basic's Date data type does not support milliseconds, and will not recognize a date with millisecond precision. This means that dates returned from SQLServer to VISUAL BASIC will be truncated. This is not an issue if you are not concerned with millisecond precision, but what if you are?

You might want millisecond precision, for example, if you are using a datetime column (lets call it update_date) for optimistic concurrency support. For example, you call a function to read a record, including the update_date. You make some changes to the record and want to save it. Before saving the record, you want to compare the update_date that you originally retrieved from the database, with the current value from that record in case is was changed sometime after you first retrieved it. In this scenario, you would probably want millisecond precision, and therefore Visual Basic's date data type will not work for you.

In a case like this, you would have a few choices.

• You could build your own Date class that implements any date functions that you need, but also supports milliseconds.
• Convert T-SQL’s datetime to use a string data type so milliseconds can be displayed. If you choose this method, you need to use caution when manipulating the data so precision is not lost.
• Use a different data type for detecting concurrency collisions. After all, even with millisecond precision, it is still possible for a collision to go undetected. Instead, for example, you could use T-SQL’s timestamp data type, which guarantees uniqueness.

Development Tool Reference

The following table summarizes the Date related capabilities of the development tools that we have discussed throughout this paper.

Tool	Data Type	Arithmetic Functions
Visual J++	SimpleDateFormat	Use Calendar class. Methods include before, after, add, roll.
Visual Basic, VBA	Date, Variant (Date sub-type)	DateAdd, DateDiff, IsDate, Date, Time, Now, DatePart, Format, TypeName, VarType, others
VBScript	Variant (Date sub-type)	DateAdd, DateDiff
Visual C++	OLEDateTime	Arithmetic operators (-,+)
ADO	ADOParameter type adDBDate	N/A
Transact SQL	datetime, smalldatetime	DATEADD, DATEDIFF

Summary

Developing applications with the Year 2000 in mind requires developer attention, regardless of the development tools being used. Using a compliant set of tools is not enough to insure an application's Year 2000 compliance. Developers must adopt a development philosophy to address Year 2000 issue in order to prevent unknowingly introducing compliance problems. This development philosophy includes:

• Always using the development tools’ date data types or otherwise ensure no loss of precision.
• Always using the development tools’ data math/comparison operators, or develop operators that ensure no loss of precision.
• Making sure the development tool and environment itself do not have Year 2000 issues.

Although the specific implementation for handling dates in the Windows DNA architecture differs slightly from tool-to-tool, the underlying concepts are the same for all tools. Using the development tool's built-in date data types and date operators will minimize the impact of the new millennium.

About The Author

Jerry Brunning is a consultant with Clarity Consulting, Inc. Clarity is a Chicago-based consulting firm that specializes in the design and development of client/server information systems. Jerry is co-author of the forth-coming book "Visual Studio Enterprise Development."

He can be reached at "mailto:jbrunning@claritycon.com" or on the Web at "http://www.claritycon.com/".

THE INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED TO CUSTOMERS FOR THE SOLE PURPOSE OF ASSISTING THE PLANNING FOR THE TRANSITION TO THE YEAR 2000. THIS DOCUMENT CONTAINS THE INFORMATION CURRENTLY AVAILABLE CONCERNING THE BEHAVIOR OF MICROSOFT'S PRODUCTS IN THE NEXT CENTURY AND IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS, PUNITIVE OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

<< 1 2 3 4 5 6 7 8 >>

This site is being designated as a Year 2000 Readiness Disclosure and the information contained herein is provided pursuant to the terms hereof and the Year 2000 Information and Readiness Disclosure Act.