John Mendez

I had to deal with converting julian dates to gregorian dates (normal date time) and vice versa recently so I wrote 2 user functions that handle converting them back and forth.  This is the first post that deals with converting a DateTime to a Julian Date.

ALTER FUNCTION [dbo].[fnConvertDateToJulian]
(
 @dtDate DATETIME
)
RETURNS DECIMAL(18,5)
AS
BEGIN

 DECLARE @a INT
 DECLARE @y INT
 DECLARE @m INT
 DECLARE @JDN INT --Julian Day Number
 DECLARE @JD DECIMAL(18,5) --Full Julian Date
 DECLARE @dayOfWeek INT
 DECLARE @date DATETIME

 SET @date = @dtDate
 SET @dayOfWeek = DATEPART(dd,@date)

 SET @a = FLOOR((14 - DATEPART(mm, @date)) / 12)
 SET @y = DATEPART(yy, @date) + 4800 - @a
 SET @m = DATEPART(mm, @date) + (12 * @a) - 3
 SET @JDN = FLOOR(@dayOfWeek + ((153 * @m + 2) / 5) + (365 * @y) + (@y / 4) - (@y / 100) + (@y / 400) - 32045)
 SET @JD = @JDN + ((DATEPART(hh, @date) - 12.00) / 24.00) + (DATEPART(mi,@date) / 1440.00) + (DATEPART(ss, @date) / 86400.00)

 RETURN @JD

END

Below are the references I used to test and get the math for it:

http://aa.usno.navy.mil/data/docs/JulianDate.php
http://www.onlineconversion.com/julian_date.htm
http://en.wikipedia.org/wiki/Julian_day

Microsoft SQL julian date, sql 2005