JD Edwards Dates in SQL Server

Recently I worked on a project where we had integration with JD Edwards.  In this project I got introduced to JD Edwards dates and times.  They are not stored as normal dates in .Net or SQL server.  But are numeric fields.

I needed to do a date comparison against a SQL Server date and a JDE date time.  To do this I created a user defined function.  This function converted the date and time fields in JDE to a SQL date I could use for comparison.

Below is the format of date and time fields in JDE.

Date Fields
The date fields are stored as six digit numbers with the format
CYYDDD
Where C = century after 1900.  So for the current century it would be 1, 1900 - 2000 = 1
YY is the year in the century for example 12 for 2012
DDD is the day of  the year.  For example April 1, 2012 is the 92nd day of 2012

Time Fields
Date and time fields are stored in separate columns in the JD Edwards database.
Time fields are stored in military time as
HHMMSS

HH = hour 1-24 not zero filled, so if its just after midnight you would have only MMSS
MM = minute 00-60 zero filled
SS = second 00-60 zero filled

Below is the user defined function I used to parse the date time.  The performance on this was not super great so to help it I put a Common Table Expression (CTE) in my stored proc.  This limited down the result set to speed up the conversion on a smaller number of records.

The performance seemed really bad when comparing SQL server datetime to the UDF result. Probably because it had to convert all JDE date times into SQL server datetime before it could do its comparison.

The UDF

1. CREATE FUNCTION [dbo].[ToGregorian](@julian varchar(6),@time varchar(6))
2. RETURNS datetime AS BEGIN
3.     DECLARE @datetime datetime,@hour int, @minute int, @second int
4.    
5.     IF(LEN(@time) = 6)
6.         BEGIN
7.             SET @hour = Convert(int,LEFT(@time,2));
8.             SET @minute = CONVERT(int,Substring(@time,3,2));
9.             SET @second = CONVERT(int,Substring(@time,5,2));
10.         END
11.     else IF(LEN(@time) = 5)
12.         BEGIN
13.             SET @hour = Convert(int,LEFT(@time,1));
14.             SET @minute = CONVERT(int,Substring(@time,2,2));
15.             SET @second = CONVERT(int,Substring(@time,4,2));
16.         END
17.     else
18.         BEGIN
19.             SET @hour = 0;
20.             SET @minute = CONVERT(int,LEFT(@time,2));
21.             SET @second = CONVERT(int,Substring(@time,2,2));
22.         END
23.    
24.     SET @datetime = DATEADD(YEAR,100*CONVERT(INT, LEFT(@julian,1))+10*CONVERT(INT, SUBSTRING(@julian, 2,1))+CONVERT(INT, SUBSTRING(@julian,3,1)),0);                     
25.     SET @datetime = DATEADD(DAY, CONVERT(INT,SUBSTRING(@julian, 4, 3))-1,@datetime);                   
26.     SET @datetime = DATEADD(hour,@hour,@datetime)
27.     SET @datetime = DATEADD(minute,@minute,@datetime);
28.     SET @datetime = DATEADD(second,@second,@datetime);
29.    
30.     RETURN @datetime
31. END