Convert Julian Date to DateTime in SQL 2005

23. July 2008 18:47

Here is the second post that has to do with converting a julian date to a datetime  This only works for dates greater than October 15th 1582 at 12:00:00.

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

 DECLARE @j INT
 DECLARE @g INT
 DECLARE @dg INT
 DECLARE @c INT
 DECLARE @dc INT
 DECLARE @b INT
 DECLARE @db INT
 DECLARE @a2 INT
 DECLARE @da INT
 DECLARE @y2 INT
 DECLARE @m2 INT
 DECLARE @d INT
 DECLARE @year INT
 DECLARE @month INT
 DECLARE @day INT
 DECLARE @hr INT
 DECLARE @min INT
 DECLARE @sec INT
 DECLARE @frac DECIMAL(18,5)
 DECLARE @f DECIMAL(18,2)
 DECLARE @finalDateTime DATETIME


 SET @frac =  @JD - FLOOR(@JD) + 0.5

 IF(@frac >= 1.0)
  BEGIN
   SET @frac = @frac - 1.0
   SET @JD = @JD + 1.0
  END

 SET @j = FLOOR(@JD) + 32044
 SET @g = @j / 146097
 SET @dg = @j % 146097
 SET @c = (@dg / 36524 + 1) * 3 / 4
 SET @dc = @dg - @c * 36524
 SET @b = @dc / 1461
 SET @db = @dc % 1461
 SET @a2 = (@db / 365 + 1) * 3 / 4
 SET @da = @db - @a2 * 365
 SET @y2 = @g * 400 + @c * 100 + @b * 4 + @a2
 SET @m2 = (@da * 5 + 308) / 153 - 2
 SET @d = @da - (@m2 + 4) * 153 / 5 + 122
 SET @year = @y2 - 4800 + (@m2 + 2) / 12
 SET @month = (@m2 + 2) % 12 + 1
 SET @day = @d + 1.5


 SET @hr  = FLOOR(@frac * 24.00)
 SET @min  = FLOOR((@frac*24.00 - @hr)*60.00)
 SET @f  = ((@frac*24.00 - @hr)*60.00 - @min)*60.00
 SET @sec = FLOOR(@f)
 SET @f = @f - @sec

 IF( @f > 0.5 )
  BEGIN
   SET @sec = @sec + 1
  END

 SET @finalDateTime = CONVERT(VARCHAR(20), CAST(@month AS VARCHAR(2))
    + '/'
    + CAST(@day AS VARCHAR(2))
    + '/'
    + CAST(@year AS VARCHAR(4))
    + ' '
    + CAST(@hr AS VARCHAR(2))
    + ':'
    + CAST(@min AS VARCHAR(2))
    + ':'
    + CAST(@sec AS VARCHAR(2)))

 RETURN @finalDateTime

END

Again here are the reference for the math and testing

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

Also to test it on your SQL Server just run the following queries once the functions are made

SELECT dbo.fnConvertDateToJulian(getDate())

SELECT dbo.fnConvertJulianToDate(dbo.fnConvertDateToJulian(getDate()))

Tags: ,

Microsoft SQL

blog comments powered by Disqus



My Random Thought

I think the OCW is a great thing to have available to those who are in school, just finished school or just want to educate themself

http://ocwconsortium.org/

John On Twitter

Discounts