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?

I was going to suggest using datetime2 and using the years 8000 - 9999 to substitute for 2000 BC to 1 BC. But I don't think this datatype is in Sql Compact edition. http://msdn.microsoft.com/en-us/library/bb677335.aspx — amelvin, Feb 20 '11 at 17:46
And do you know the significance of the year 1753 here? And what that means for your project? And it should have been 1586, but enfin. — H H, Feb 20 '11 at 18:50
@Henk: You might check this question regarding the the year 1753 limitation: [What is the significance of 1/1/1753 in SQL Server?](http://stackoverflow.com/q/3310569/456051) — fnx, Feb 21 '11 at 08:27

score 8 · Accepted Answer · answered Feb 20 '11 at 17:47

8

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

answered Feb 20 '11 at 17:47

BrokenGlass

158,293
28
286
335

1

The project looks promising. I hope there is a stable version in the next view months. I like that it supports partial dates, because there are many historical events where the month or day is not known. There is a [presentation on infoq.com](http://www.infoq.com/presentations/Save-the-Day-with-Noda-Time) by Jon Skeet. – fnx Feb 21 '11 at 18:54

score 2 · Answer 2 · answered Feb 20 '11 at 18:29

2

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

answered Feb 20 '11 at 18:29

Joel Coehoorn

399,467
113
570
794

I thought about storing the date in 3 separate columns, because sometimes there is only the year and month known of a specific event. You could use 1712-01-01 for example, but that is actually not correct data. Of course you're losing the validation part of the Datetime Type (does this date really exist for example). – fnx Feb 21 '11 at 17:27
1

@fnx Using int works for years because once you get really old the calendar system is different, and all the built-in date validation code get's thrown out the window anyway. – Joel Coehoorn Feb 21 '11 at 17:32

score 2 · Answer 3 · edited Jan 12 '12 at 14:10

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

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

3 Answers3

Linked