Fuzzy Data Matching with SQL

Chapter 5. Dates, Dates, Dates

There are three basic problems with dates:

• They are impossibly hard.

• They can be represented by an almost infinite number of culture formats.

• Even taking all that into account, they can be misentered.

In this chapter we’ll discuss all of these problems and various approaches to handling them.

Time Is Relative

Why are dates “impossibly hard”? I used to work on a CRM system that, depending on the user’s locale setting, could display to an end user a birth date a day in the future or past of what the person whose nativity it represented thought was their birthday. Why? Because it didn’t force all birth dates into simply a date-only format like YYYY-MM-DD. After all (thought that system’s designers), you may want to capture what time your customer (your patient’s new child?) was born. So the birth date field was of course a SQL DATETIME datatype. Obviously. Makes sense.

Except that the user interface to that system had no way of specifying the time component, or for that matter the time zone, of the birth date in mention, and so would simply record whatever was the current time and time zone for the user entering the data (and also for any system pushing data into it). Hence, if your birth date was April 1, 1990, and the person entering that into the system was on the West Coast of the United States at 4:45 p.m., then your birth time would be entered in that timestamp as 4:45PM PST (or PDT depending on the time of year, oh joy). And guess what? ...