5.4 Storing Out-of-Range Temporal Values5.4.1 ProblemYou 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 SolutionUse 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 DiscussionThis 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. |