A Date With PowerBuilder  Y2K and other date related issues which affect PowerBuilder applications

Last Updated 02 June 1998

• Introduction
• What is Y2K compliance?
• PowerBuilder supported date range
• Problematic areas in Date interpretation
• Ensuring correct Date interpretation
• Problematic area in Date storage
• Ensuring correct Date storage
• Problematic areas in processing Dates
• Ensuring correct Date processing
• A Final checklist

Introduction

    Y2K compliance has become a raging issue in the developer and the user community. I have discussed this issue with a lot of PowerBuilder developers and the response has varied from inordinate paranoia to off-guard complacency. While I agree that there is probably no need for PB developers to panic, it is true that a lot of PowerBuilder applications developed in recent times and the ones currently being developed  are susceptible to the Y2K problem. The problem is aggravated by the disparate manner in which dates are used in a PowerBuilder application. This article aims to explorer some of the areas in a typical PowerBuilder application where the Y2K anomaly can creep in.

    The information presented here is based on my experience in developing applications with PowerBuilder and Sybase SQL Server and the following technical documents available at the Sybase tech support site-

1. PowerSoft Date Support for the 21st Century - Doc ID 5139
2. Sybase Product Compliance to Year 2000 Industry Standards White Paper - Doc ID 20030

  I strongly recommend that you read the white paper (Doc ID 20030) even if you are not using Sybase as your database.
 

What is Y2K compliance?

    I really don't want to get into the nitty-gritty details of defining the Y2K problem itself. There is already a lot of technical literature devoted to that. You may check out the Sybase white paper for a comprehensive definition of Y2K compliance and Y2K compliance standards. To put it in a nutshell, we want our applications to -

1. Correctly interpret the user input, particularly the century part
2. Correctly store and retrieve dates from a database
3. Correctly perform date mathematics and other processing involving dates, with dates across the year 2000

PowerBuilder supported date range

Problematic areas in date interpretation

    It is often perceived that the Y2K problem is limited to incorrect storage of dates - like the old COBOL programs which only stored the last two digits of the year part to save space. While the incorrect storage problem has been greatly alleviated by modern day DBMS, it is infact the PowerBuilder user interface which is most susceptible to the millennium bug due incorrect interpretation of the user input. Here are some potential pitfalls-
 
 

2 Digit Year part in EditMasks

Typical PowerBuilder applications make use of the EditMask window control or the EditMask datawindow column edit style to input dates. Problems might arise if the EditMask specifies only 2 digits in the year part. For example - 'dd/mm/yy' or 'mm/dd/yy'. This leaves the interpretation of the year to PowerBuilder which interprets it as follows-

• If the year is between 00 and 49 then the first two digits are assumed to be 20
• Else if the year is between 50 to 99 PowerBuilder assumes 19

Possible Problems

1. When the user intends to input a date like 1942, the application will incorrectly interpret it as 2042
2. And as a corollary, when presented with a date like 01-11-22, the user will not be able to decide if the year is 1922 or 2022

International [date] EditMasks

I have seen a few applications, where in the zealousness to follow the Windows GUI standards the GUI designers went to the extent of specifying the date EditMasks as '[date] International'. The idea is that the EditMask should appear as per the Windows Short Date Format settings on the user's machine. If you have noticed, Microsoft applications like Schedule+ and Outlook sport this kind of a behavior.

Possible Problems

1. The user might specify a date format with only 2 digits in the year part. As a result, the problems mentioned in the '2 Digit year part in EditMasks' situation will apply here too.
2. This situation may actually be worse, since some users will input correct dates while others will goof up, creating a mess which will be almost impossible to clean up.
3. Since the date format maybe inconsistent on different machines, when a user switches between machines, it is possible that he might inadvertently input incorrect dates. Example the user inputs dates in the 'dd/mm/yyyy' format, unaware that the date format on the machine is set to 'mm/dd/yyyy'.

I am a huge fan of the Windows GUI and I usually put in a lot of effort to ensure that my applications have the Windows 'look and feel'. But this is where I draw the line. Integrity of enterprise data is of paramount importance. Jeopardizing this integrity for the sake of relatively frivolous features like 'look and feel' is foolish.
 

Using the plain Edit style for date input

It's hard to believe that there are applications which don't use EditMasks at all to input dates. I saw one jut last month. In such a situation, PowerBuilder expects the dates to be in the Windows short date format.
 

Possible Problems

1. All the problems discussed in the preceding situation apply here too.

Converting strings to dates and the effect of the Windows Short-Date format

The PowerBuilder function Date(String) is used to convert a string to date. The IsDate() function checks if a string contains a valid date.  As you can see there is no way one can specify the format in which the date is to be interpreted. Something like Oracle's ToDate function in which one can indicate the date format would have been useful. So how does PowerBuilder figure out the date format? It doesn't. Here is how it works -
 

1. First, the date is parsed into three parts using separator characters like  / - , . : or (space)
2. If the first part specifies 4 digits then PowerBuilder assumes the date format to be of the form - Year-Month-Day. It then goes ahead and converts the string to a date. Thus dates like '1998-12-22' and '2020-Feb-12' are always correctly interpreted and converted.
3. If the first part does not specify 4 digits, then PowerBuilder assumes the date format to be of the same form as the Windows Short-Date format. Note that I say 'the same form' and not 'the same format'. By 'form' I mean the 'order' of the Year, Month and Day parts. For example 'dd-mm-yyyy' and 'dd-MMM-yy' are of the same form.

Possible Problems

1. If the date format does not specify 4 digits then the interpretation depends on the Windows short date format. If the Windows short date format is different than the date string's format then the Date(String) function will return the date '1900-Jan-01'.
2. Dates might get misinterpreted. Example if the Windows Short-Date format is 'dd-mm-yyyy' and the string to be converted is in the 'mm-dd-yyyy' format then a date like '02-12-1995'(1995-Feb-12) will be interpreted as '1995-Dec-02'.

The lack of awareness of the effect of the Windows Short-Date format can have severe repercussions. I know of a team which faced a lot of problems while developing an application with PowerBuilder 4.0. During the early stages of development the developers had trouble converting strings to dates. After some shallow investigating they arrived at the conclusion that PowerBuilder expects the date format to be 'mm/dd/yyyy' to correctly convert a string to a date. Unaware that this just happened to be the Windows Short-Date format on the developer's machine, they went ahead with developing the application under the assumption that PowerBuilder expects the date format to be 'mm/dd/yyyy'. The project was a huge one and it was only one and half years later that they realized the connection between the Windows Short-Date format and PowerBuilder's interpretation of dates. Too late to change the mammoth amount of code already written with the incorrect  assumption, the developers tried a lot of ways to force the short date format on the user's machine to 'mm/dd/yyyy'. Finally, the only reliable solution they could come up with was to specify in the User's Manual that the Windows Short-Date format has to be set to 'mm/dd/yyyy'. Needless to say, this caused a lot friction with the user community.

Ensuring correct Date interpretation

As you may have realized out by now, PowerBuilder intemperately depends upon the Window Short-Date format to interpret dates. The risk of misinterpretation of dates can be greatly reduced by eliminating this dependency. Taking care that the Year part of a date always specifies all the four digits will ensure that the Y2K bugs do not creep in to the application. Here are a few guidelines-

• Always use an EditMask for Date input
• All EditMasks across the entire application should be standardized to a date format which specifies a 4 digit Year part. Personally, I am in favor of 'yyyy-mm-dd' or 'yyyy-mmm-dd'. But I have found that it is very difficult to get the users to accept these formats. So, 'mm-dd-yyyy', 'dd-mm-yyyy' etc. are good enough as long as they specify a 4 digit Year part.
• Do not specify the date format as [date] international
• When converting a String to a Date use the Date(Int Year, Int Month, Int Day) function. Example:
• 
  //First break up the date string into it's constituent parts
  //Of course, to break it up we need to know the format the string is going to
  //be in. This can be ensured by having an application standard format. Assuming mm-dd-yyyy here-

  Str_Date = "12-25-1925"
  Int_Year = Integer(Right(Str_Date,4))
  Int_Mon  = Integer(Left(Str_Date,2))
  Int_Day  = Integer(Mid(Str_Date, 4,2))

  dt_myDate =Date(Int_Year, Int_Mon, Int_Day)

  Ideally you should put this in a global function or make it a part of a date service like PFC's n_cst_datetime.

Problematic areas in Date storage

    As I said earlier, most popular DBMS do not have a problem with storing dates greater than 2000. However problems might occur while manipulating data. Even though the DBMS can handle dates beyond 2000 nothing can prevent a disaster if you specify only two digits in the year part or use an ambiguous date format in which the DBMS cannot clearly figure out the Month and the Day parts.
    As far as I know, whenever a DataWindow generates an SQL statement it uses an unambiguous date format to specify dates. You should confirm this by using either the 'trace' option or by viewing the SQL generated in the DataWindow's SQLPreview event.

Ambiguous date formats in SQL

When using Embedded SQL and Dynamic SQL, if you specify the date in an ambiguous format then interpretation of the date is left to the DBMS and you run the risk of the date being misinterpreted. Again, a two digit year date format will cause a lot of problems with interpretation. How the DBMS interprets a 2 digit year may vary from DBMS to DBMS and you should check your DBMS' policy on this issue. Also, you should check if the DBMS expects the date to be in a particular format. For example in case of Oracle, the date must be specified as per the date format specified in the NLS_DATE_FORMAT parameter (else you must use the ToDate() function).

Ensuring correct Date storage

• Always use four digits when specifying a date
• When using Embedded SQL instead of specifying the date as a quoted string use a Date variable.

For example instead of using specifying the date in the following manner-

  Insert Into MyTable
  Values(1, "Jiggy", '1996-01-01')   ;

Use a PowerBuilder Date variable-

Date dt = 1996-01-01
Insert Into MyTable
  Values(1, "Jiggy", :dt)   ;
 

• Check if the DBMS expects the date to be specified in a particular format

Problematic areas in processing Dates

How you use dates in processing will largely depend on the application you are developing. However here are some typical examples of processes that involve dates -

• Date comparison and date mathematics
• Date dependant algorithms (Example - Interest calculation over a period)
• Data purging/archiving based on dates

Ensuring correct Date processing

If you have ensured correct date interpretation and correct date storage then you have little to worry about in this area. But bugs might creep due to subtle mistakes in programming. Here are some guidelines-

• Make sure that all programmers strictly use 4 digit year date formats when converting strings to dates and follow the guidelines mentioned previously to ensure that the conversion does not depend on the Windows short-date format.
• If possible, have code walk-throughs to spot potential problems in algorithms involving dates. If more people look at the algorithm the probability of find a subtle bug will improve.
• Rigorously test such algorithms over a wide range of dates. Make sure that you check the case when part of dates being processed are on either side of the year 2000. This is where most of the problems occur.

A Final Checklist

• Most of the problems can be avoided if you use an unambiguous 4 digit Year Date format wherever Dates are involved.
• Beware of programming bugs in complex algorithms involving dates.
• Understand how your DBMS handles dates.

Jiggy, 22^nd March 1998

Back to my HomePage