What is the best way to handle bc dates in .net / sql server?

Question

I'm planning to create a timeline application that stores and displays information for specific dates. For example: Aristotle 384 BC - 322 BC; but also ad dates like Immanuel Kant 22.04.1724 - 12.02.1804).

I want to use a sql compact edition database. The datetime format allows dates from 1/1/1753 12:00:00:00 AM to 12/31/9999 11:59:59 PM. So I can't use the native datetime format. .Net also doesn't allow bc dates using the native DateTime class (0001.01.01 is the first day).

Do I have to define my own format using a varchar column and in .Net parse those values?

Answer 1

You could have a look at noda-time, a port of Joda-time for .NET, which does handle historic dates (but you'd still have to handle persistence yourself).