JULIAN TO GREGORIAN DATE UDF

Description

This User Defined Function (UDF) is used to transform or cast the Julian date format to a Gregorian date format. Julian dates can be received in three different formats such as JD Edwards World, astronomy or ordinary format.

Custom UDF overloads

JULIAN_TO_GREGORIAN_DATE_UDF(julianDate, formatSelected)

It returns a string with the Gregorian date format YYYY-MM-DD.

Parameters:

JulianDate: The Julian date to be cast. It can be either CYYDDD (where C is the century) or YYYYDDD.

formatSelected: It represents the format in which the Julian date should be processed. Besides, it is a CHAR and can accept the following formats:

Format available	Letter representation in CHAR	Description
Astronomy standardized	'J'	It is the default format. The cast is based in the expected conversion of the Astronomical Applications Department of the US. The Julian Date format for this is YYYYDDD.
JD Edwards World	'E'	The expected Julian date to be received in this case should be CYYDDD (where C represents the century and is operationalized to be added 19 to the corresponding number).
Ordinal dates	'R'	The ordinal dates are an arrangement of numbers which represent a concisely date. The format is YYYYDDD and can be easily read because the year part is not mutable.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.JULIAN_TO_GREGORIAN_DATE_UDF(JULIAN_DATE CHAR(7), FORMAT_SELECTED CHAR(1))
RETURNS variant
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    const CONST_FOR_MODIFIED_JULIAN_DATE = 0.5;
    const BEGINNING_OF_GREG_CALENTAR = 2299161;
    const CONST_AFTER_GREG_VALUE = 1867216.25;
    const DIVIDENT_TO_GET_CENTURY = 36524.25;
    const LEAP_YEAR_CONSTANT = 4;
    const CONST_TO_GET_DAY_OF_MONTH = 30.6001;

    //Functions definitions

    function julianToGregorian(julianDate){
        const JD = julianDate + CONST_FOR_MODIFIED_JULIAN_DATE; //setting modified julian date 
        const Z = Math.floor(JD); //setting fractional part of julian day
        const F = JD - Z; //fractional part of the julian date
        let A, alpha, B, C, D, E, year, month, day;
        
        //verification for the beginning of gregorian calendar
        if(Z < BEGINNING_OF_GREG_CALENTAR){ 
            A=Z; 
        } else {
            //alpha is for dates after the beginning of gregorian calendar
            alpha = Math.floor((Z-CONST_AFTER_GREG_VALUE) / DIVIDENT_TO_GET_CENTURY);
            A=Z+1+alpha - Math.floor(alpha/LEAP_YEAR_CONSTANT);
        }

        B = A + 1524;
        C = Math.floor((B-122.1)/365.25);
        D = Math.floor(365.25*C);
        E = Math.floor((B-D)/CONST_TO_GET_DAY_OF_MONTH);

        day= Math.floor(B-D-Math.floor(CONST_TO_GET_DAY_OF_MONTH*E)+F);
        month=(E<14)? E -1: E-13;
        year=(month>2)? C-4716: C-4715;

        return new Date(year, month-1, day);
    }

function cyydddToGregorian(julianDate){
        var c=Math.floor(julianDate/1000);
        var yy=(c<80)? c+2000: c+1900;
        var ddd=julianDate%1000;
        var date= new Date(yy, 0);
        date.setDate(ddd);
        return date;
    }

function ordinalDate(ordinalDate){
    const year = parseInt(ordinalDate.toString().substring(0,4));
    const dayOfYear = parseInt(ordinalDate.toString().substring(4));
    const date = new Date(year, 0); //Set date to the first day of year
    date.setDate(dayOfYear);
    return date;
}

function formatDate(toFormatDate){
    toFormatDate = toFormatDate.toDateString();
    let year = toFormatDate.split(" ")[3];
    let month = toFormatDate.split(" ")[1];
    let day = toFormatDate.split(" ")[2];
    return new Date(month + day + ", " + Math.abs(year)).toISOString().split('T')[0]
}

    switch(FORMAT_SELECTED){
        case 'E': 
            //JD Edwards World formar, century added  - CYYDDD
            var result = formatDate(cyydddToGregorian(parseInt(JULIAN_DATE)));
            return result;
        break; 
        case 'J':
            //astronomical format YYYYDDD
            return formatDate(julianToGregorian(parseInt(JULIAN_DATE)));
        break;
        case 'R':
            //ordinal date format YYYYDDD
            return formatDate(ordinalDate(parseInt(JULIAN_DATE)));
        break;
        default: return null;
    }
    
$$
;

Usage Example

Oracle

Query

IN -> Oracle_01.sql

select to_date('2020001', 'J') from dual;

Result

TO_DATE('2020001', 'J')
18-JUN-18

Formatted result

TO_CHAR(TO_DATE('2020001', 'J'), 'YYYY-MON-DD')
0818-JUN-18

• Note: The date must be formatted in order to visualize all digits of the year.

Snowflake

Query

OUT -> Oracle_01.sql

select
PUBLIC.JULIAN_TO_GREGORIAN_DATE_UDF('2020001', 'J')
from dual;

Result

JULIAN_TO_GREGORIAN_DATE_UDF('2020001', 'J')
"0818-06-18"

Know Issues

1. Any other format: If the Julian Date is formatted in any other not supported format, there would be differences in the output.

2. Ranges of B.C. dates may represent inconsistencies due to unsupported Snowflake functions for dates.

Related EWIs

No EWIs related.