The Microsoft« Windows DNA architecture represents a comprehensive new framework for integrating client/server and internet software solutions on the Windows platform. Windows DNA is based on the premise of software component integration and interoperability. DNA combines "component based" software development with a "service based" operating system. However, integrated software components and modularity do not protect the developer from introducing Year 2000 compliance problems in their applications. Regardless of the tools used to develop software today, a clear Year 2000 philosophy is needed to avoid spending time reworking an application in future years.

Year 2000 compliance issues are best avoided by strict use of the development tools' date data types and date functions when working with date-related data. Most development tools have built in rules for handling date arithmetic that will avoid Year 2000 compliance problems. Developers should validate that the development tools they are using are Year 2000 compliant and the rules and logic used by the tools for date handling.

Year 2000 Overview

The Year 2000 problem itself is fairly simple: software designed to store date-related information using only the last two digits for the year may not perform as expected when representing dates from the year 2000 and beyond. Since the century is assumed by these programs to always be 19, the year 2000, for example, will be interpreted by these programs as 1900. Non-Year 2000 compliant software will cease to function properly because date-related calculations will not return the expected results. For example, a program that calculates the difference in years between 1978 and 1999 using two-digit date notation would work correctly (99 - 78 = 21 years); whereas a program that calculates the difference in years between 1978 and 2001 using two-digit date notation would not yield the correct result (01 - 78 = -77).

Many applications only deal with current and historical dates. These types of applications, when not Year 2000 compliant, will only exhibit problems when the year 2000 comes upon us. However, many applications in production today are already exhibiting Year 2000 problems. For example, many credit card authorization system had trouble dealing with card expiration dates beyond 1999 (which are generally represented in MM/YY form).

The impact of non-compliance is the failure of the software to perform as expected. In the worst case, total application failure can be expected. At the very least, misrepresented or corrupted data can make its way into the corporate database. This corrupt data may be used as the basis for making key business decisions or, for example, may fail to generate an exception or flag a customer's record when expected.

What exactly is Year 2000 compliance? Microsoft defines a Year 2000 compliant product as one that "will not produce errors processing date data in connection with the year change from December 31, 1999 to January 1, 2000 when used with accurate date data, provided all other products (e.g., other software, firmware and hardware) used with it properly exchange date data with the Microsoft product."

Does this mean that if you are using all Microsoft development tools, or all Year 2000 compliant development tools, that the applications that you develop will automatically be Year 2000 compliant? Not at all. In fact, Microsoft issues a disclaimer with their development tools regarding Year 2000 compliance that states "Compliance refers to the Microsoft product as delivered by Microsoft. The Compliance Statement does not apply to user customizable features or third party add-on features or products, including items such as macros and custom programming and formatting features." What this amounts to is that while the development tool is itself Year 2000 compliant, it is still up to the developer to practice development techniques that insure compliance is maintained in their custom applications.

How did the problem occur?

In order to fully understand the magnitude of the problems presented by the Year 2000, it is helpful to understand historically how the problem occurred.

With mainframe based software development in the '70s, conservation of expensive resources like memory was critical. Developers were able to realize significant memory savings by shortening the year portion of date fields from four characters to just two. Developers today may find this difficult to understand. Considering that each variant data type littered throughout today's typical ASP page consumes over 22 bytes of memory, conserving those additional two bytes seems dubious. But the practice of assuming that the century was the 19th reportedly allowed companies to realize millions of dollars in savings. The need to critically scrutinize every byte of memory used, coupled with the fact that applications were not expected to stand the test of 30 years of time, has in essence created the Year 2000 issue for the information technology industry. Other Year 2000 problems, such as the incorrect calculation of a leap year, or using a "dummy" date such as "9/9/99" as a flag are generally smaller in scope. These problems are related to the lack of insight on the part of the developer, or, in the case of an incorrect leap year calculation, an un-trapped error.

Mainframe-based "legacy" software written decades ago isn't the sole contributor to the problem. Actually, software written today continues to exhibit problems of compatibility with the next millennium. In almost all cases, the problem can be attributed to just two causes:

(1) Developers continue to take shortcuts by representing the year portion of a date with just two digits.

In most parts of society, two digits are used to represent the year portion of a date. The most common way to represent a date in the United States is in the format "mm/dd/yy". It is a custom that people have used in their everyday lives for decades. This custom isn't generally a problem because humans can determine the correct year based on the context in which it is used. Therefore, a four-digit year isn't always necessary.

Think about it: when you sign a contract or write a check, do you write the date's full year, or do you use mm/dd/yy notation? Probably the latter. Unfortunately, computer code is written by humans, and humans have difficulty overcoming their habits.

The general public often scoffs at our industry's Year 2000 problem. The general public, however, will likely face their own personal psychological Year 2000 problems after 1999 when performing everyday tasks like writing checks and computing people's ages. Maybe then they will be more sympathetic to our old code!

Computers cannot make assumptions based on context but instead must apply a single rule to interpret the correct date. The century must be assumed either through the selection of some arbitrary number (19) or by applying a simple rule that determines the century based on the two digit year in question. The 32-bit Microsoft operating systems (with current service packs installed) assume the century is 19 if the two-digit year is 30 or greater. For two-digit years less than 30, the century is assumed to be 20. For software coded today using dates with two-digit years, the computer is essentially guessing the correct date because the developer failed to correctly utilize the tools available to them.

(2) Developers use development tools that don't properly handle date related data, or don't use the date data type correctly.

Many development tools today provide a special data type for handling date-related data. In addition, many provide special functions for performing date conversions, date comparisons, and date math. Using these functions is necessary to prevent inaccuracy, or loss of precision when working with date and time data. Tools that don't provide these services force the developer to substitute a character or string data type, or a less accurate numeric data type. Unfortunately, date math is somewhat complex, and the developer is forced to implement this complexity. For example, developers are required to programmatically deal with leap years and months that contain different numbers of days. In many situations, developers simply ignore these exceptions or deal with them incorrectly.

Distributed interNet Applications Architecture

The Windows Distributed interNet Applications (DNA) architecture, introduced by Microsoft in September 1997, represents a comprehensive new framework for integrating client/server and internet software solutions on the Windows platform. The goal of the DNA architecture is to integrate the development model for applications so that they can harness the power and advanced processing capabilities of today's PCs, yet still take advantage of the flexibility and ease of deployment of the Web.

Windows DNA is based on the premise of the integration and interoperability of software components. DNA combines "component based" software development with a "service based" operating system. One of the ways Microsoft accomplishes the goals of DNA is by tightly integrating the core application infrastructure into the operating system. By building functions like transaction processing, message queuing, security and directory services into the operating system, Microsoft has freed the developer from spending a significant amount of project resources creating the application infrastructure. In addition, since these services are provided by the operating system, all levels of the application - the user interface, business services, data services - can use them as needed. This means that a developer writing a web-based front-end and another creating server-side business components both have access to the underlying infrastructure.

Component-based software is assembled from independent pieces that interrelate to perform a specific function. Components written to the Windows DNA architecture have well-defined COM interfaces that make them extendable and subscribe to or utilize services provided by the operating system which makes them compatible. For example, a developer writing a component that needs to implement asynchronous messaging can use the interface published by the operating system and know that it will be compatible with any other messaging-enabled component written to that interface.

Windows DNA applications are fundamentally different from most of today's corporate applications. That's because Windows DNA lets developers take advantage of Internet, LAN-based, and client/server computing -- enabling the applications they create to do more at a lower cost.

Overview of the Sample Application

For the remainder of this paper, we'll look at a sample application that is built upon the Windows DNA architecture. The application was designed to highlight the proper development techniques for handling dates when using Microsoft Windows DNA-based development tools. Since code snippets from the application will be used throughout the next sections, you may want to open the sample application for reference. The application can be opened using Visual Studio to open the workspace Y2k.dsw from the folder that you selected at installation time. The technologies used include Visual Basic Scripting Edition« (VBScript), JavaScript, Active Server Pages, Microsoft Transaction Server (MTS), ActiveX« Data Objects and the HTML subset of DHTML. The development tools used include Visual InterDevÖ web development system, Visual J++ development system for Java, Visual Basic and Visual C++« development systems from Microsoft Visual StudioÖ, as well as Microsoft SQL ServerÖ. There is an alternate client portion written as a Microsoft Excel 97 worksheet. Don't worry if you do not have all of the Developer Studio development tools installed on your system. As long as you're not going to recompile the application, you won't need all of the tools.

Requirements for running the sample application

Running the sample application is not necessary in order to follow this paper, but may help you to understand the context of some of the code snippets. If you wish to run the application, make sure you are running Windows NT« Server 4.0 Service Pack 3 or later, with Windows NT Option Pack installed, along with SQL Server 6.5 with Service Pack 2 or later. The application needs to create a table in the "pubs" database. If you do not plan on running the sample application, you can elect not to create the table. The COM components (written in Visual Basic and Visual C++) can be imported into MTS by importing the package file Y2kPackage installed in the folder Y2K\MTS. This step is optional; the application will still function correctly outside of MTS.

The application tracks the accounts receivable of a fictional company. The user enters transactions for one of three customers, and then views an accounts receivable aging report. The report summarizes the dollar amount outstanding for the time periods 0-30 days, 31-60 days, 61-90 days, etc. The application is very simple in its design, and is not meant to illustrate a solution to any particular business problem. Nor is it meant to be an example of an ideal architecture using Windows DNA. Rather, it is intended to be used as a mechanism for understanding the underlying techniques used when handling date-related data, including date validation, date formatting, and data arithmetic.

The application addresses all of the pieces of a relatively complex Windows DNA "helix". There are two client portions to the application: a web-based version that uses a Visual J++ applet as the main user interface for data entry; and an Excel 97 worksheet version that interfaces directly with the business component. Notice how in each scenario below a date passes from component-to-component.

For the Excel-based client:

1. The user enters values directly into cells in an Excel worksheet.
2. The worksheet uses VISUAL BASIC macros to perform the client-side data validation.
3. The data is then passed to a VISUAL BASIC component running in an MTS process on the server.
4. The VISUAL BASIC component validates the data, partially through the use of a C++ COM component also hosted in MTS.
5. If the data is validated, the VISUAL BASIC component uses ADO (via the OLEDB provider for ODBC) to store the data in the "pubs" database on SQL Server.

1. Javascript on the web page queries the applet for values entered by the user and passes them to the web server for processing via an HTTP Post.
2. ASP code on the server receives the data.
3. ASP passes the data to a VISUAL BASIC component running in an MTS process on the server.
4. The VISUAL BASIC component validates the data, partially through the use of a C++ COM component also hosted in MTS.
5. If the data is validated, the VISUAL BASIC component uses ADO (via the OLEDB provider for ODBC) to store the data in the "pubs" database on SQL Server.

The application consists of the following:

Development Philosophy

It is helpful to establish an overall "Year 2000 compliance" philosophy when setting out to develop an application. A philosophy can then be integrated into the application's design strategy, or the company's development strategy as a whole. The philosophy for achieving Year 2000 compliance is fairly straight forward:

• Always use the development tools' date data types or otherwise insure no loss of precision.
• Always use the development tools' data math/comparison operators, or develop operators that insure no loss of precision (including exception handling, such as leap years).
• Make sure the development tool and environment itself do not have Year 2000 issues (e.g. check out http://www.microsoft.com:80/y2k for Microsoft's compliance). Be sure to consider all of the tools being used throughout the system, including things like report writers, third party add-ons and controls, etc. Also be sure to consider the underlying infrastructure, such as the data access drivers and network protocols.

DNA Integration - Working with the sample code

Client Portion: Java

The sample application presents a simple user interface using an applet written in Java on a web page (Figure 1). JavaScript interacts with the applet to receive the values for the Customer ID, Transaction Date, Amount, Terms and Due Date. Before receiving the data, it first calls the applet's CheckData() function (Listing 1).

Figure 2 - The web-based user interface.

The CheckData() function first verifies that all of the required fields have been entered. Next, it checks that the value entered for the Transaction Date is a valid date. This is accomplished by using the Java SimpleDateFormat class (java.text.SimpleDateFormat), which is a subclass of the DateFormat class (java.text.DateFormat). In the Java language, a date is represented as a specific millisecond in time.

public String CheckData()
	{
		//Validates the data.  
		//Returns an error message if data is invalid.
		//Otherwise, just returns an empty string.
		
		Calendar transDate = Calendar.getInstance();
		Calendar dueDate = Calendar.getInstance();

		//Check for required fields...
		if (m_cboCustomer.getSelectedItem().length() == 0)
			return "A customer is required.";
		if (m_txtDate.getText().trim().length() == 0)
			return "A transaction date is required.";
		if (m_txtDate.getText().trim().length() != 10)
			return "The transaction date is invalid.  Proper format is 'mm/dd/yyyy'";	
		if (m_txtAmount.getText().trim().length() == 0)
			return "A transaction amount is required.";
		if (m_cboTerms.getSelectedItem() == "Due Date")
			if (m_txtDueDate.getText().trim().length() == 0)
				return "A due date is required.";
			else if (m_txtDueDate.getText().trim().length() != 10)	
				return "The due date is invalid.  Proper format is 'mm/dd/yyyy'";	
				

		//Validate the dates entered using the pattern mm/dd/yyyy.
		//If I try to call parse() for an invalid date, an exception
		//occurs, which is caught by the exception handler

		//Specify the date format that I'm expecting.
		SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
		df.setLenient(false);
		
		//Attempt to create a date from the text entered by the user.
		try {
			transDate.setTime(df.parse(m_txtDate.getText()));
		}
		catch (Exception e) {
			//If the user entered an invalid date, return an error message
			return "The transaction date is invalid.  Proper format is 'mm/dd/yyyy'";	
		}
		
		
		//Also validate the due date, if one was entered.
		if (m_cboTerms.getSelectedItem() == "Due Date") {
			try {
				dueDate.setTime(df.parse(m_txtDueDate.getText()));
				//make sure due date is the same or later 
				//than the transaction date...
				if (transDate.after(dueDate)) {
				return "The due date cannot be earlier than the transaction date.";
				}
			}
			catch (Exception e) {
				return "The due date is invalid.  Proper format is 'mm/dd/yyyy'";	
			}	
		}	
		
		//Make sure the amount entered is numeric.
		try {
			Float.valueOf(m_txtAmount.getText()).floatValue();
		}	
		catch (Exception e) {
			if (e.getClass().toString().equals("class java.lang.NumberFormatException"))
				return "Invalid numeric amount.";
			else
				return e.getClass().toString();
		}	
		
		//Everything is Ok, just return an empty string.
		return "";
	}						

//Specify the date format that we're expecting.
SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
df.setLenient(false);

The second line in Listing 2 tells the SimpleDateFormat class not to be lenient when interpreting dates. It is important to tell the SimpleDateFormat class not to be lenient when handling dates. If you don't, Java will not verify that the data entered is actually a valid date, rather it will only verify that it is in the proper format. For example, the invalid date in Listing 3 will not generate an exception because the format of the date is valid. The default value for setLenient is true, so always change it to false when working with the SimpleDateFormat class.

//This will not generate an exception, even though the date is invalid.
SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
Date dt = new Date();
dt = df.parse("02/29/1997");

//Attempt to create a date from the text entered by the user.
try {
  transDate.setTime(df.parse(m_txtDate.getText()));
}
catch (Exception e) {
  //If the user entered an invalid date, return an error message
  return "The Transaction Date is invalid.  Proper format is 'mm/dd/yyyy'";	
}

Now that we know all of the date information is valid, the applet will enforce one business rule: the due date cannot be earlier than the transaction date. In order to do this, we need to compare two dates. When comparing dates or performing date arithmetic, always use the development tool's operators. In Java, the SimpleDateFormat class is used for formatting and parsing only. The class used for date arithmetic and comparisons, including time zone conversions, is the Calendar class (java.util.Calendar). The Calendar class can be used to extract a certain portion of a date (such as the month or the year) or to perform comparisons on two dates, as displayed in Listing 5.

transDate.setTime(df.parse(m_txtDate.getText()));
dueDate.setTime(df.parse(m_txtDueDate.getText()));
	
//make sure due date is the same or later 
//than the transaction date...
if (transDate.after(dueDate)) {
  return "The Due Date cannot be earlier than the Transaction Date.";

The Calendar's after() function, which also takes a Calendar is a parameter, returns true if the date or time of the Calendar passed as a parameter is after the date or time of this Calendar. Comparing the two Calendar objects, then, is just a matter of calling the after() function on the Transaction Date's Calendar and passing in as a parameter the Due Date's Calendar. If it is false, we will return an error message; otherwise we return an empty string and processing continues. By using the Calendar class's built in date comparison functionality, we've removed all possibility of programmer date math bugs or lack of precision.

Once the data has been validated by calling the applet's CheckData() function, we are ready to send it to the server to be further processed and eventually saved to the database. We'll get the data from the applet to the web server through the use of an HTML POST method. The HTML POST method is used to send data from a form tag on a web page to the server to be processed. In our example, all of the inputs on the form are of type hidden, which means the browser will not make them visible to the user. We can still store values in the hidden fields, then send them to the server by using POST.

To get the values from the applet to the HTML form, we call the applet's Customer(),Date(), Amount(), Terms() and DueDate() functions and copy the values returned from those functions into the form fields. Listing 6 shows the JavaScript function add() which was taken from the Active Server Page that gets the values from the applet. This is the code that gets called when the user clicks the Add button on the client page (refer to Figure 1).

function add() {

var sRet = document.Y2kApplet.CheckData();	
if (sRet != "") {
	alert(sRet);
	return false;
}
else {
  with(document.forms[0]) {
	hidCustomer.value = Y2kApplet.Customer();
	hidDate.value = Y2kApplet.TransDate();
	hidAmount.value = Y2kApplet.Amount();
	hidTerms.value = Y2kApplet.Terms();
	hidDueDate.value = Y2kApplet.DueDate();
	submit();
  }
}

The data is received by an Active Server Page that parses out the values POSTed to it. The data is then passed to a Visual Basic (VB) component running under MTS, which saves it to the database. The Active Server Page uses VBScript to create an instance of the VISUAL BASIC component. It then calls either the AddByDate method or the AddByDay method (Listing 7), depending on whether the user specified a due date or not.

Working with Variants

VBScript has only one data type-Variant-which can contain a representation of many other data types. In addition, each Variant has a sub-type that further describes the underlying value it has been assigned. The sub-types consists of the more strongly types data types from VISUAL BASIC (String, Long, Integer, Boolean, Date, Single, Double, etc.) When a Variant is initially assigned a value, VISUAL BASIC automatically chooses the appropriate sub-type based on the value being assigned. The following table illustrates how VBScript will assign sub-types to the variable MyVariant:

Note that in the last example in the table above, VBScript assigned the String sub-type to MyVariant, instead of the Date sub-type. VBScript will always choose the String data type when being assigned character data.

Remember our development philosophy, "always use the tool's date data type"? When using VBScript or Variants, adhering to this rule is a little bit tricky. Variants play fast and loose with their sub-types, often changing them without the developer even realizing it. Basically, we need to make sure that when storing date information in a Variant that we always force VISUAL BASIC to sub-type our information to the Date sub-type. That is, we need to make sure that the information in the Variant is of the Date sub-type.

We can do this by assigning the Variant's value something that it can not interpret as anything other than a date. In the example above, what looked to a human like a date ("5/5/1998") was interpreted (and thus assigned) as a string sub-type. However, as we will see in the example, we could have explicitly coerced VISUAL BASIC into assigning the Variant a date sub-type if we had explicitly converted the string "5/5/1998" into a date value using the CDate() function.

Our VISUAL BASIC component expects the Transaction Date and the Due Date parameters to be date data types (see Listing 7). To send the values for these fields as dates to the VISUAL BASIC component, we use the VBScript function CDate(). CDate() takes one parameter (a variant of course) and attempts to convert it into a date. If the parameter passed into the CDate() function cannot be converted into a date, a Type Mismatch error will occur.

The CDate() function uses the same logic as the VBScript function IsDate(), which returns true if its argument is a valid date It is important to use CDate() to force variants to the Date sub-type to prevent compliance problems. This can be illustrated in the following example:

VariantX = "5/5/1998"	Sub-type of VariantX is String
VariantY = "6/31/1998"	Sub-type of VariantY is String
DateX = CDate(VariantX)	Sub-type of DateX is Date
DateY = CDate(VariantY)	A Type Mismatch error occurs because VariantY cannot be converted to a date.

You can determine the sub-type of any Variant in VBScript by using the TypeName() function. The TypeName function takes a Variant as a parameter, and returns its sub-type. This function comes in very handy when debugging. Passing the wrong data type to an external function or procedure (as illustrated in the table above) is a common mistake, and is difficult to uncover.

Another useful function is the IsDate() function. IsDate() takes a Variant as a parameter. It returns True if the parameter can be converted to a valid date, False if it can not. IsDate() is useful for validating whether or not user input (or external input) is a valid date.

Dim Y2kComponent

If Request.QueryString("mode") = "add" then
Set Y2kComponent = Server.CreateObject("Y2kVB.Component")
If Request.Form("hidTerms") = 0 then
 Call Y2kComponent.AddByDate(Request.Form("hidCustomer"), _
	CDate(Request.Form("hidDate")), _
	Csng(Request.Form("hidAmount")), _
	CDate(Request.Form("hidDueDate")))
Else
 Call Y2kComponent.AddByDays(Request.Form("hidCustomer"), _
	CDate(Request.Form("hidDate")), _
	Csng(Request.Form("hidAmount")), _
	CInt(Request.Form("hidTerms")))
 End If
 Set Y2kComponent = Nothing
End If

In addition to the Java/Internet client, the sample application includes a Microsoft Excel client piece. Complex solutions can be created using Excel's VBA capabilities. Using VBA, the Excel client will connect directly to the VISUAL BASIC component running in MTS. For simplicity, the Excel client mimics the logic of the Java client. The VBA CheckData() validation function is similar to the function that we used in Java-they differ only in language syntax (see Listing 8). The syntax for handling dates in VBA is identical to Visual Basic, which we will discuss in the next section.

Function CheckData() As String

'Used for client side validation

Dim sRet As String

With ActiveSheet
    
    'Get the customer name
    m_Customer = Worksheets("Data").Cells(ActiveSheet.Range("Customer"), 1).Value
    
    'Make sure that a customer was chosen.
    If m_Customer = "" Then
        CheckData = "A customer is required."
        Exit Function
    End If
    
    'Make sure that a transaction date was entered.
    If .Range("TransDate") = "" Then
        CheckData = "A transaction date is required."
        Exit Function
    
    'Check the transaction date to make sure it is valid.
    ElseIf Not IsDate(.Range("TransDate")) Then
        CheckData = "The transaction date is invalid.  Proper format is 'mm/dd/yyyy'"
        Exit Function
    Else
        m_TransDate = .Range("TransDate")
    End If
    
    'Make sure that a due date was entered.
    If .Range("Terms") = 5 Then
        m_Terms = 0
        If .Range("DueDate") = "" Then
            CheckData = "A due date is required."
            Exit Function
        End If
        If Not IsDate(.Range("DueDate")) Then
            CheckData = "The due date is invalid.  Proper format is 'mm/dd/yyyy'"
            Exit Function
        End If
            
        m_DueDate = .Range("DueDate").Value
    Else
        m_Terms = Left(Worksheets("Data").Cells(.Range("Terms"), 2).Value, 2)
    End If
    
    m_Amount = Val(.Range("Amount"))
    
    'Verify that the amount is >= 0
    If m_Amount <= 0 Then
        CheckData = "A transaction amount is required to be greater than 0."
        Exit Function
    End If
    
End With
    
End Function

Our VISUAL BASIC business component is invoked through a call to either its AddByDate method or AddByDay method. Both of these methods add a record to the database. The AddByDate method is used when the user entered the actual due date for the receivable, whereas the AddbyDay method is used if the user just selected a number of days from the Terms. Listing 9 contains the code for both of these functions.

The VISUAL BASIC component validates the data passed to it from its consumer (either the ASP page or the Excel client). Because this component represents a "business component", it validates all parameters, regardless of whether or not they were previously validated. The business component should function as a "black box", meaning that it can be called from any type of client and should not assume that client side validations were performed.

Public Sub AddByDays(ByVal Customer As String, ByVal TransactionDate As Date, ByVal Amount As Single,
ByVal Terms As Integer)

On Error GoTo Receivable_EH

Dim Cmd As adodb.Command
Dim dtDueDate As Date
Dim DateMath As Y2KSAMPLELib.DateMath

    Set DateMath = CreateObject("DateMath.DateMath.1")
    dtDueDate = DateMath.DateAddX(Terms, TransactionDate)
    Set DateMath = Nothing
    
    'Check some business rules...
    Call CheckBusinessRules(Customer, TransactionDate, Amount, dtDueDate, "AddByDays")
    
    Set Cmd = CreateObject("ADODB.Command")
    Cmd.ActiveConnection = CONNECT_STRING
    
    Cmd.CommandType = adCmdStoredProc
    Cmd.CommandText = "y2k_addReceivable"
    Cmd.Parameters("@customer").Value = Customer
    Cmd.Parameters("@date").Value = TransactionDate
    Cmd.Parameters("@amount").Value = Amount
    Cmd.Parameters("@due_date").Value = dtDueDate
    
    Cmd.Execute
    Set Cmd = Nothing

    If Not ctxContext Is Nothing Then
        Call ctxContext.SetComplete
    End If

    Exit Sub

Receivable_EH:
    Set Cmd = Nothing
    If Not ctxContext Is Nothing Then
        Call ctxContext.SetAbort
    End If
    
    Call RaiseError("AddByDays", Err.Number, Err.Description)

End Sub


Public Sub AddByDate(ByVal Customer As String, ByVal TransactionDate As Date, ByVal Amount As Single,
ByVal DueDate As Date)

On Error GoTo Receivable_EH

Dim Cmd As adodb.Command
    
    'Check some business rules...
    Call CheckBusinessRules(Customer, TransactionDate, Amount, DueDate, "AddByDate")
    
    Set Cmd = CreateObject("ADODB.Command")
    Cmd.ActiveConnection = CONNECT_STRING
    
    Cmd.CommandType = adCmdStoredProc
    Cmd.CommandText = "y2k_addReceivable"
    Cmd.Parameters("@customer").Value = Customer
    Cmd.Parameters("@date").Value = TransactionDate
    Cmd.Parameters("@amount").Value = Amount
    Cmd.Parameters("@due_date").Value = DueDate
    
    Cmd.Execute
    Set Cmd = Nothing
    
    If Not ctxContext Is Nothing Then
        Call ctxContext.SetComplete
    End If

    Exit Sub

Receivable_EH:
    Set Cmd = Nothing
    If Not ctxContext Is Nothing Then
        Call ctxContext.SetAbort
    End If
    
    Call RaiseError("AddByDate", Err.Number, Err.Description)

End Sub

In addition, VISUAL BASIC has straight forward date math functions such as DateAdd for adding dates and DateDiff for determining the difference between two date. For comparing dates, we can just use the same comparison operators we would use for numeric data ("<", "<=", ">", ">=", etc.). As long as both of the data types we are comparing are date data types, VISUAL BASIC will accurately compare the two values. However, using these operators with values that are not valid dates will trigger a run-time error.

Listing 10 shows the VISUAL BASIC code used in the function CheckBusinessRules to verify that the Due Date is not earlier than the Transaction Date.

'The Due Date cannot be earlier than the transaction date.
    If DueDate < TransactionDate Then
        Call RaiseError(ErrorSource:=Source, _
                        ErrorNumber:=vbObjectError + 1002, _
                        ErrorDescription:="Due Date cannot be earlier than Transaction Date.")
    End If

For example, the Debug.Print statement in Listing 11 will very likely always return false. This is because d2 is assigned the value of the Now function, which returns the current date and time, whereas d1 is assigned the value of just the date. When the time is not specified in a Date data type, it defaults to 12:00:00 AM. Unless the Now function was also executed at this exact time, the Debug.Print will return false.

Thus, if you are only interested in working with a date (as opposed to a more precise date/time), make sure to not unknowingly give it time precision as well.

Dim d1 As Date
Dim d2 As Date

d1 = Date
d2 = Now

Debug.Print (d1 = d2)	'will return false, except if run at exactly midnight.

Dim d1 As Date
Dim d2 As Date

d1 = Date
d2 = Now

Debug.Print DateDiff("d", d1, d2)  'returns 0.

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;
}

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;
}

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.

1. You could build your own Date class that implements any date functions that you need, but also supports milliseconds.
2. 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.
3. 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.

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.

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."