5.4 Storing Out-of-Range Temporal Values

5.4.1 Problem

You are creating a database for an archeologist, and the archeologist needs to store dates that are outside the range of Transact-SQL's temporal datatypes.

5.4.2 Solution

Use the ISO 8601 format, preferably without the dashes between date elements, and store the data as string:

CREATE TABLE Archive(
   EventId CHAR(40),
   EventDate CHAR(8)
)

Now, insert a few dates and note the results:

INSERT INTO Archive 
  VALUES ('Columbus departs from Palos, Spain', '14920802')
INSERT INTO Archive 
  VALUES ('Columbus arrives at Cuba', '14921029')
INSERT INTO Archive 
  VALUES ('Columbus returns to Spain', '14930315')


SELECT * FROM Archive

EventId                             EventDate  
----------------------------------- ---------- 
Columbus departs from Palos, Spain  14920802  
Columbus arrives at Cuba            14921029  
Columbus returns to Spain           14930315 

5.4.3 Discussion

This is, of course, the old programmer's trick for representing temporal data in languages that don't have native support for it. It's designed for AD dates and such dates can easily be sorted properly. There are, however, two additional issues that need emphasizing.

First, don't use the 'YYYY-MM-DD' format when using the technique illustrated in this recipe. If you do use that format, don't forget to include leading zeros for all date elements. Dates such as '1492-8-2' cannot be sorted properly with respect to '1492-10-12'. If you must include dashes in your dates, you should include leading zeros, as in: '1492-08-02'.

The second issue, more of a feature than a problem, is that you can easily extend this format to include hours, minutes, and even seconds. Simply include the requisite number of digits in the date string. This isn't to say, though, that we understand the need for such precise temporal values for dates prior to the 18th century.