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
- CREATE FUNCTION [dbo].[ToGregorian](@julian varchar(6),@time varchar(6))
- RETURNS datetime AS BEGIN
- DECLARE @datetime datetime,@hour int, @minute int, @second int
- IF(LEN(@time) = 6)
- BEGIN
- SET @hour = Convert(int,LEFT(@time,2));
- SET @minute = CONVERT(int,Substring(@time,3,2));
- SET @second = CONVERT(int,Substring(@time,5,2));
- END
- else IF(LEN(@time) = 5)
- BEGIN
- SET @hour = Convert(int,LEFT(@time,1));
- SET @minute = CONVERT(int,Substring(@time,2,2));
- SET @second = CONVERT(int,Substring(@time,4,2));
- END
- else
- BEGIN
- SET @hour = 0;
- SET @minute = CONVERT(int,LEFT(@time,2));
- SET @second = CONVERT(int,Substring(@time,2,2));
- END
- SET @datetime = DATEADD(YEAR,100*CONVERT(INT, LEFT(@julian,1))+10*CONVERT(INT, SUBSTRING(@julian, 2,1))+CONVERT(INT, SUBSTRING(@julian,3,1)),0);
- SET @datetime = DATEADD(DAY, CONVERT(INT,SUBSTRING(@julian, 4, 3))-1,@datetime);
- SET @datetime = DATEADD(hour,@hour,@datetime)
- SET @datetime = DATEADD(minute,@minute,@datetime);
- SET @datetime = DATEADD(second,@second,@datetime);
- RETURN @datetime
- END
