SQL Date Types and Ranges in No Year

in XML Schema there is a gMonthDay type which represents dates such as October 31; that is, Halloween but not in any particular year. How is this typically handled in SQL? The date type requires a year? Do I just have to define separate INT fields for month and day?

A related question: how are date ranges handled? e.g. the range from Halloween to Christmas (10-31 to 12-25)? Two columns for the start and two for the end? Or a start date followed by a number of days? Leap years make this tricky though.

And still a third question: ranges may extend for the entire year or a part thereof. Furthermore they may extend across the New Year’s boundary. e.g. their could be a range that goes from 11-3 to 3-15. How is that handled?

And finally, to really complicate matters the range may be discontiguous. That is, it could cover March 15 to May 15 and September 15 to October 15, but not the intervening dates. However, the potential discontiguity is limited. In my application, there are never more than two contiguous ranges within a year.

I can hack this together, but surely I’m not the first person to need something like this. Has anyone seriously worked through a problem like this and published a detailed analysis of the different approaches for modelling this in SQL tables, and the advantages and disadvantages of each? References appreciated.

This entry was posted on Monday, November 21st, 2005 at 8:11 PM and is filed under Databases. You can follow any responses to this entry through the Atom feed. You can make a comment, Digg this story, or trackback from your own site.