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

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 (0开发者_开发百科001.01.01 is the first day).

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

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).

Store the year separately as a signed integer. If you need a specific date within that year keep a separate datetime column and just use a standard value for the year part (1900 is common).

The best advice here is to not rely on the Microsoft for datetime values. The better solution is to write your own Julian date class and deal with the conversion from a written date (such as above with Aristotle) to a Julian date.

1/1/322 BC = 1603447.5
1/1/384 BC = 1580801.5

Sub GetJD(mn%, dy%, yr%, ByRef JD#)
    Dim c#
    c# = 12# * (yr% + 4800) + mn% - 3
    JD# = Int(c# / 48) + Int((365 * c# + 2 * (c# - 12 * Int(c# / 12)) + 7) / 12) + dy% - 32083
    If JD# > 2299170! Then JD# = JD# + Int(c# / 4800) - Int(c# / 1200) + 38
    JD# = JD# - 0.5 ' = 0 hrs GMT
End Sub