How to represent end-of-time in a database?

Use the max collating date, which, depending on your DBMS, is likely going to be 9999-12-31. You want to do this because queries based on date ranges will quickly become miserably complex if you try to take a "purist" approach like using Null, as suggested by some commenters or using a forever flag, as suggested by Marc B.

When you use max collating date to mean "forever" or "until further notice" in your date ranges, it makes for very simple, natural queries. It makes these kind of queries very clear and simple:

• Find me records that are in effect as of a given point in time.
  ... WHERE effective_date <= @PointInTime AND expiry_date >= @PointInTime
• Find me records that are in effect over the following time range.
  ... WHERE effective_date <= @StartOfRange AND expiry_date >= @EndOfRange
• Find me records that have overlapping date ranges.
  ... WHERE A.effective_date <= B.expiry_date AND B.effective_date <= A.expiry_date
• Find me records that have no expiry.
  ... WHERE expiry_date = @MaxCollatingDate
• Find me time periods where no record is in effect.
  OK, so this one isn't simple, but it's simpler using max collating dates for the end point. See: this question for a good approach.

Using this approach can create a bit of an issue for some users, who might find "9999-12-31" to be confusing in a report or on a screen. If this is going to be a problem for you then drdwicox's suggestion of using a translation to a user-friendly value is good. However, I would suggest that the user interface layer, not the middle tier, is the place to do this, since what may be the most sensible or palatable may differ, depending on whether you are talking about a report or a data entry form and whether the audience is internal or external. For example, some places what you might want is a simple blank. Others you might want the word "forever". Others you may want an empty text box with a check box that says "Until Further Notice".

In PostgreSQL, the end of time is 'infinity'. It also supports '-infinity'. The value 'infinity' is guaranteed to be later than all other timestamps.

create table infinite_time (  ts timestamp primary key);insert into infinite_time values(current_timestamp),('infinity');select *from infinite_timeorder by ts;2011-11-06 08:16:22.078infinity

PostgreSQL has supported 'infinity' and '-infinity' since at least version 8.0.

You can mimic this behavior, in part at least, by using the maximum date your dbms supports. But the maximum date might not be the best choice. PostgreSQL's maximum timestamp is some time in the year 294,276, which is sure to surprise some people. (I don't like to surprise users.)

2011-11-06 08:16:21.734294276-01-01 00:00:00infinity

A value like this is probably more useful: '9999-12-31 11:59:59.999'.

2011-11-06 08:16:21.7349999-12-31 11:59:59.999infinity

That's not quite the maximum value in the year 9999, but the digits align nicely. You can wrap that value in an infinity() function and in a CREATE DOMAIN statement. If you build or maintain your database structure from source code, you can use macro expansion to expand INFINITY to a suitable value.

We sometimes pick a date, then establish a policy that the date must never appear unfiltered. The most common place to enforce that policy is in the middle tier. We just filter the results to change the "magic" end-of-time date to something more palatable.