Convert a JDE Julian Date (CYYDDD) to YYYY-MM-DD format in XSLT or SQL

I have a condition where I need to convert a JDE Julian Date (CYYDDD) to YYYY-MM-DD format in XSLT. Can anyone help with either some SQL query or any other way that we can do it XSLT.

I really appreciate your help. Thanks in advance.

Solution

Here are a couple of solutions for dealing with JDE Julian Dates (which differ from the official definition a Julian date). For JDE:

The units, tens & hundreds part of the number refers to days since the start of the year; with '001' corresponding to 1st January.
Everything in the thousands and above corresponds to the year; with 100 representing the year 2000.
Example: 100001 is 2000-01-01

Both solutions below work in the same way:

Date divided by 1000 & result floored (rounded down) gives the year part of the number (i.e. custs off the last 3 digits). e.g. 123321 => 123.
Adding 1900 to this gives us the actual year. e.g. 123 + 1900 => 2023
We then convert this to a valid date by building up a date string for 1st Jan and converting the string to a date; e.g. 2023 -> 2023-01-01.
Date modulus 1000 returns the days offset part of the figure (i.e. essentially cuts off all but the last 3 digits). e.g. 123321 => 321.
We take 1 from the days offset (i.e. because 1st Jan is 001; not 000). e.g. 321 => 320
We then offset our 1st Jan date calculated from the year part by the number of days from the day part. e.g. 2023-01-01 + 320 days => 2023-11-17

MS SQL

create function dbo.ConvertJulianToDate (
    @JulianDate integer
)
returns date 
begin
    return dateadd(
       day
       , @JulianDate % 1000 - 1
       ,cast(
           cast(
               @JulianDate / 1000 + 1900 
               as varchar(4)
           ) + '-01-01' 
           as date
       )
    ) 
end

Example Usage

select dbo.ConvertJulianToDate(117175)

XSLT 2.0

<xsl:template name="JulianToDate">
    <xsl:param name="JulianDate" select="./text()" />
    <xsl:variable name="year" select="1900 + floor(($JulianDate div 1000))" />
    <xsl:variable name="days" select="($JulianDate mod 1000) - 1" />
    <xsl:value-of select="xs:date(concat($year,'-01-01')) + xs:dayTimeDuration(concat('P',$days,'D'))"/>
</xsl:template>

Full Code

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:transform 
    version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:fn="http://www.w3.org/2005/xpath-functions"
    exclude-result-prefixes="xsl xs fn"
>
<xsl:output method="xml" encoding="UTF-8" indent="yes" />

    <xsl:template match="/*">
        <Root>
            <CallOnCurrentElement>
                <xsl:call-template name="JulianToDate" />
            </CallOnCurrentElement>
            <PassValue>
                <xsl:call-template name="JulianToDate">
                    <xsl:with-param name="JulianDate">114026</xsl:with-param>
                </xsl:call-template>
            </PassValue>
        </Root>
    </xsl:template>

    <xsl:template name="JulianToDate">
        <xsl:param name="JulianDate" select="./text()" />
        <xsl:variable name="year" select="1900 + floor(($JulianDate div 1000))" />
        <xsl:variable name="days" select="($JulianDate mod 1000) - 1" />
        <xsl:value-of select="xs:date(concat($year,'-01-01')) + xs:dayTimeDuration(concat('P',$days,'D'))"/>
    </xsl:template>
</xsl:transform>

Sample XML

<?xml version="1.0" encoding="UTF-8"?>
<julianDate>117175</julianDate>

Fiddle Demo: http://xsltransform.net/6pS1zCU

XSLT 1.0

Date functions aren't available in XSLT 1.0, so it's very hacky. However I believe this should work.

    <xsl:template name="JulianToDate">
        <xsl:param name="JulianDate" select="./text()" />
        <xsl:variable name="year" select="1900 + floor(($JulianDate div 1000))" />
        <xsl:variable name="days" select="($JulianDate mod 1000)" />
        <xsl:variable name="isLeapYear" select="(($year mod 4) = 0) and ((($year mod 100) != 0) or (($year mod 400) = 0))" />
        <xsl:variable name="month" select="1" />
        <xsl:variable name="day" select="1" />
        <xsl:variable name="febDays">
            <xsl:choose>
                <xsl:when test="$isLeapYear">29</xsl:when>
                <xsl:otherwise>28</xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
        <xsl:choose>
            <xsl:when test="31 >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days" />
                    <xsl:with-param name="MM" select="1" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(31 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days -31" />
                    <xsl:with-param name="MM" select="2" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(63 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (31 + $febDays)" />
                    <xsl:with-param name="MM" select="3" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(92 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (63 + $febDays)" />
                    <xsl:with-param name="MM" select="4" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(123 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (92 + $febDays)" />
                    <xsl:with-param name="MM" select="5" />
                    <xsl:with-param name="yyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(153 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (123 + $febDays)" />
                    <xsl:with-param name="MM" select="6" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(184 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (153 + $febDays)" />
                    <xsl:with-param name="MM" select="7" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(215 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (184 + $febDays)" />
                    <xsl:with-param name="MM" select="8" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(245 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (215 + $febDays)" />
                    <xsl:with-param name="MM" select="9" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(276 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (245 + $febDays)" />
                    <xsl:with-param name="MM" select="10" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(306 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (276 + $febDays)" />
                    <xsl:with-param name="MM" select="11" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:when test="(337 + $febDays) >= $days">
                <xsl:call-template name="CreateDate">
                    <xsl:with-param name="dd" select="$days - (306 + $febDays)" />
                    <xsl:with-param name="MM" select="12" />
                    <xsl:with-param name="yyyy" select="$year" />
                </xsl:call-template>
            </xsl:when>
            <xsl:otherwise>
                <xsl:message terminate="yes">Invalid Julian Date</xsl:message>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>
    <xsl:template name="CreateDate">
        <xsl:param name="yyyy" />
        <xsl:param name="MM" />
        <xsl:param name="dd" />
        <xsl:value-of select="concat(substring(10000 + $yyyy, 2),'-',substring(100 + $MM, 2),'-',substring(100 + $dd, 2))"/>
    </xsl:template>

(This just replaces the equivalent template from the XSLT 2.0 version; all else (version number aside) should be identical.