Julian Gregorian Date Bridge

Recently I was tasked with pulling dates out of a JDEdwards source system, which stores dates in a Julian style format. This is pretty typical of the mainframe era when they were initially built, and we began discussing how to pull the data out and converting it for consumption in a date table in the data warehouse. As the data was being staged prior to loading into the warehouse, it made sense to convert the dates into actual readable gregorian dates as they were being brought into the staging environment. Initially it was discussed to create a function that you could call for any date that you wanted to convert, but that approach had difficulties in that you would have to call the function for every date that you wanted. A colleague suggested a date bridge table, and I came up with a script that populates it. Below is my script:

CREATE TABLE dbo.JulianGregorianBridge
(JulianDate int NOT NULL,
GregorianDate int NOT NULL
)

DECLARE @StartDate DATE
,@EndDate DATE

SET @StartDate = ‘1/1/1949′
SET @EndDate = ’12/31/2050′

WHILE @StartDate <= @EndDate

BEGIN

INSERT INTO dbo.JulianGregorianBridge(
JulianDate
,GregorianDate)
SELECT
(Year(@StartDate) -1900) * 1000 + datediff(dd, ’12/31/’ + cast(year(@StartDate) -1 as varchar(4)), @StartDate) AS ‘JulianDate’
,YEAR(@StartDate) *10000 + MONTH(@StartDate) *100 + DAY(@StartDate) AS [DateKey]

SET @StartDate = DATEADD(d, 1, @StartDate)
END
GO

Straightforward, simple, and best of all, efficient. The alternative design approach using a function was more challenging as every function call would have caused row by row evaluation of the date to convert it, with this pseudo-date dimension table you can do a lookup between the date you want to convert and the Julian evaluation to return the smart key integer, or go from smart key to Julian with high performance.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s