Jerry Brunning
Clarity Consulting, Inc.
August 1998
Click to copy the vs6y2k sample application discussed in this article.
Summary: Outlines a multilanguage sample application that shows the proper, Year 2000-compliant techniques for handling dates. Some familiarity with Visual Basic, Java, and C++ will be helpful. (22 printed pages) Discusses:
Year 2000 (Y2K) compliance permeates every facet of software development. Problems occur when software is designed to store, transmit, pass, or calculate date-related information using only the last two digits for the year. Under these circumstances, the year 2000 is interpreted by these programs as 1900, which may result in the failure of the software to perform as expected. Year 2000 compliance problems generally exist in legacy applications, but they are also introduced into new applications because developers take code shortcuts to save space in data records and databases, as well as improperly use date functions and data types. Developers need to be aware of the impact of noncompliance, as well as development techniques to insure compatibility in the next millennium.
The Microsoft® Windows® Distributed interNet Applications (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.
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. Because 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 systems had trouble dealing with card expiration dates beyond 1999 (which are generally represented in MM/YY form).
The impact of noncompliance 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 (for example, 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, 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.
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 Active Server Pages (ASP) page consumes more than 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 untrapped 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:
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.
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.
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 they can harness the power and advanced processing capabilities of today's computers, 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, because these services are provided by the operating system, all levels of the application—the user interface, business services, and 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 Component Object Model (COM) interfaces that make them extendable and subscribe to or utilize services provided by the operating system that 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, local area network (LAN)-based, and client/server computing—enabling the applications they create to do more at a lower cost.
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 Windows DNA-based development tools. Because 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 Microsoft 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 Microsoft Visual InterDev Web development system, Microsoft Visual J++ development system for Java, Microsoft Visual Basic and Visual C++ development systems from 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 Microsoft 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.
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 version 4.0 Service Pack 3 or later, with Windows NT Option Pack installed, along with SQL Server version 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, and so on. 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.
The following diagram outlines the structure of the sample application.
Figure 1. The sample application architecture
The application consists of the following.
| File(s) | Description | Location |
| Y2k.dsw | Visual Studio workspace | Y2K |
| Y2kApplet.dsp | Visual J++ project for Java applet | Y2K\JavaApplet |
| Y2K.xls | Excel 97 worksheet user interface | Y2K\Excel |
| Y2kSample.dsp | Visual C++ project for C++ COM component | Y2K\C_helper |
| Y2kWeb.dsp | Web project—ASP pages and Web files | Y2K |
| Y2k.VBP | Visual Basic project for Visual Basic component | Y2K\VB |
| Y2kPackage.pak | MTS package | Y2K\MTS |
| Y2k.dat | Database definition file | Y2K\data |
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 straightforward:
The sample application subscribes to and illustrates this philosophy.
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.
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 "";
}
The SimpleDateFormat class in Java is used to format and parse date and time data. It can be used to convert a string to a date, or a date back to a string, and format it with the specified format mask. You can specify a formatting mask to use during the constructor:
//Specify the date format that we're expecting.
SimpleDateFormat df = new SimpleDateFormat("MM/dd/yyyy");
df.setLenient(false);
This indicates that whenever we format a date using this instance of the SimpleDateFormat class, Java will use the format "MM/dd/yyyy." This causes the date May 31, 1998, to be formatted as 5/31/1998. Remember that Java is a case-sensitive language. Using the lowercase letter "m" instead of uppercase will not produce the same results—the lowercase letter "m" is used for the minutes portion when specifying a formatting mask for a time.
The second line in the preceding code 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 the following code 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");
In order to verify that the dates entered by the user are valid, we need to convert them into Java's Date data type. We can do this by calling SimpleDateFormat's parse() function. The parse() function takes a string and attempts to convert it into a Date data type. If the string cannot be converted to a date, an exception will be raised, which we can trap and use to return an error message:
//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'";
}This type of procedure should be followed with any other dates entered by an end user. By attempting to convert string data stored in the applet's TextField into Java's native Date data type, we are assured that the information entered is in fact a valid date.
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 tools' 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:
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.";
Before using the Calendar class, we must assign it a date. We do this with the setTime() function. This function assigns a date, a time, or both to the Calendar. The setTime() function takes a date as its argument. We previously parsed the contents of the TextFields into dates for the Transaction Date and Due Date when we were validating those fields. We can move the results into their respective Calendars at the same time.
The Calendar's after() function, which also takes a Calendar as 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, and 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. The following code 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, depending on whether the user specified a due date or not.
VBScript has only one data type, Variant, which can contain a representation of many other data types. In addition, each Variant has a subtype that further describes the underlying value it has been assigned. The subtype consists of the more strongly typed data types from Visual Basic (String, Long, Integer, Boolean, Date, Single, Double, and so on) When a Variant is initially assigned a value, Visual Basic automatically chooses the appropriate subtype based on the value being assigned. The following table illustrates how VBScript will assign subtypes to the variable MyVariant.
| MyVariant = "Hello" | Subtype is String. |
| MyVariant = 123.456 | Subtype is Double. |
| MyVariant = 15 | Subtype is Integer. |
| MyVariant = "5/5/1998" | Subtype is String. |
Note that in the last example in the preceding table, VBScript assigned the String subtype to MyVariant, instead of the Date subtype. VBScript will always choose the String data type when being assigned character data.
Remember our development philosophy, "always use the tools' Date data type"? When using VBScript or Variants, adhering to this rule is a little bit tricky. Variants play fast and loose with their subtypes, often changing them without the developer even realizing it. Basically, we need to make sure that, when storing date information in a Variant, we always force Visual Basic to subtype our information to the Date subtype. That is, we need to make sure that the information in the Variant is of the Date subtype.
We can do this by assigning the Variant's value something that it cannot interpret as anything other than a date. In the preceding example, what looked to a human like a date ("5/5/1998") was interpreted (and thus assigned) as a String subtype. However, as we will see in the example, we could have explicitly coerced Visual Basic into assigning the Variant a Date subtype 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. 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 subtype in order to prevent compliance problems. This can be illustrated in the following example.
| VariantX = "5/5/1998" | Subtype of VariantX is String. |
| VariantY = "6/31/1998" | Subtype of VariantY is String. |
| DateX = CDate(VariantX) | Subtype of DateX is Date. |
| DateY = CDate(VariantY) | A Type Mismatch error occurs because VariantY cannot be converted to a date. |
You can determine the subtype of any Variant in VBScript by using the TypeName() function. The TypeName function takes a Variant as a parameter, and returns its subtype. This function comes in very handy when debugging. Passing the wrong data type to an external function or procedure (as illustrated in the preceding table) 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 cannot. IsDate() is useful for validating whether 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 Visual Basic for Applications (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. 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. The following code 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 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
Continuing with our philosophy, we are using the Date data type in Visual Basic for the Transaction Date and the Due Date. This is very convenient because, in Visual Basic, a run-time error will be raised if we attempt to pass nondate values to either of these parameters.
In addition, Visual Basic has straightforward date math functions such as DateAdd for adding dates and DateDiff for determining the difference between two dates. 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.
The following 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
Note that the Date data type also stores time information. Use caution when comparing dates in Visual Basic using the comparison operators, because if your date variables also store times (even unknowingly), the times will be compared as well.
For example, the Debug.Print statement 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 not to 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.
If you are working in date/time precision, but need to just compare dates, consider using the DateDiff function. DateDiff can be used to find the number of days that occur between two dates. Using DateDiff, the preceding code can be rewritten as shown below. Because DateDiff is used with the "d" parameter, only the day portion of the two dates is compared:
Dim d1 As Date
Dim d2 As Date
d1 = Date
d2 = Now
Debug.Print DateDiff("d", d1, d2) 'returns 0.
The AddByDays routine 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++, which 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 the example earlier in "The Business Component: Visual Basic" for the syntax for creating an instance of the COM components from Visual Basic, and for calling the DateAddX function. The following displays the C++ DateAddX function:
STDMETHODIMP CDateMath::DateAddX(IN long Number, IN DATE Date, OUT DATE * NewDate)
{
*NewDate = Date + Number;
return S_OK;
}
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. Because 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, because 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. 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 SQL Server 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 (let's 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 it 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 such as this, you would have the following choices:
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 subtype) | DateAdd, DateDiff, IsDate, Date, Time, Now, DatePart, Format, TypeName, VarType, others |
| VBScript | Variant (Date subtype) | DateAdd, DateDiff |
| Visual C++ | OLEDateTime | Arithmetic operators (-,+) |
| ADO | ADO Parameter type adDBDate | N/A |
| Transact SQL | datetime, smalldatetime | DATEADD, DATEDIFF |
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 issues in order to prevent unknowingly introducing compliance problems. This development philosophy includes:
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 tools' built-in Date data types and date operators will minimize the impact of the new millennium.
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 coauthor of the forthcoming book Visual Studio Enterprise Development.
He can be reached at jbrunning@claritycon.com or on the Web at www.claritycon.com/.