Julian Dates

I need to work with Julian dates since they will facilitate some MDX queries I am creating for customer lifetime value. Unfortunately SQL Server does not support Julian dates but they’re easy to create so that’s what today’s discussion is all about. I’ll be using the dates more than once so creating a user defined function is probably a good idea.

User defined functions consist of several parts. Data types of parameters and return values must be specified and you have to name the function so you can call it. For more information, check out books on line. You’re probably wondering why the functions start with January 1, 1753. This is where SQL Server starts with date arithmetic so we can’t go back any further than that. The Julian value of that date is 2361331 so that’s where that number comes from. These functions simply add or subtract integer values from that baseline. I normally don’t hard code values into programs but the baseline for Julian dates is constant so it will never change.

Here are the two functions. Just type them into the SQL Query editor and run them. They will automatically be created and are visible in the Programmability/Functions/Scalar Functions folder of the database you created them in.

CREATE FUNCTION udf_datetojul (@any_dte datetime)
RETURNS INTEGER
AS
BEGIN
RETURN DATEDIFF(D,'1/1/1753',@any_dte)+2361331
END

CREATE FUNCTION udf_jultodate (@julian_dte INT)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(D,@julian_dte-2361331,'01/01/1753')
END

Here’s the call to convert the system date to a Julian value:

SELECT dbo.udf_datetojul(getdate())

If you want more information on Julian dates, here’s a great reference: http://aa.usno.navy.mil/data/docs/JulianDate.html