Idea Excursion

I stumbled upon an interesting concept for dealing with dates: store them in Julian day format. At first glance, I only knew the format to be some sort of ID linking to a calendar table, but in fact, the IDs were designed to match to the equivalent Julian date. This is an ingenious way to store a date for a really great reason: you get an easy-to-index key and also can coerce a meaning from the data, all in 4 bytes. Need to link to a calendar table to determine which fiscal quarter the date falls in? No problem, the join is right there. Only need the actual date for output? No join needed, just convert the value to Gregorian date. Here are a couple of conversion functions for that specific task.

Convert Julian to Gregorian:

1
2
3
4
5
6
7
8

CREATE FUNCTION dbo.f_JulianToGregorian
(
	@JulianDay INT
) RETURNS DATETIME
AS
BEGIN
	RETURN DATEADD(dd, @JulianDay - 2415021, 0)
END

Convert Gregorian to Julian:

1
2
3
4
5
6
7
8

CREATE FUNCTION dbo.f_GregorianToJulian
(
	@GregorianDate DATETIME
) RETURNS INT
AS
BEGIN
	RETURN DATEDIFF(dd, 0, @GregorianDate) + 2415021
END

Some examples:

SELECT
	dbo.f_JulianToGregorian(2454467) [J2G]
	, dbo.f_GregorianToJulian('2008-01-01') [G2J]
	, dbo.f_JulianToGregorian(dbo.f_GregorianToJulian('2008-01-01')) [G2J2G]
	, dbo.f_GregorianToJulian(dbo.f_JulianToGregorian(2454467)) [J2G2J]

And results:

J2G                     G2J         G2J2G                   J2G2J
----------------------- ----------- ----------------------- -----------
2008-01-01 00:00:00.000 2454467     2008-01-01 00:00:00.000 2454467

In case you’re wondering, 2415021 is the number of days between January 1, 4713 B.C. (the start of the Julian calendar) and January 1, 1900 (the start of Microsoft’s date counting). Because we are limited to SQL Server’s date calculation ranges, we can only convert back to 1753 A.D. If you are fortunate enough to be working on a SQL Server 2008 system, feel free to change the datetime to datetime2, or better yet, the simple type of date. Either of those new data types will give you access to years 1 through 9999 A.D. Although I’m sure this is not a concern for most people, unfortunately, SQL Server does not provide native functionality to deal with B.C. dates.