DATE TO JULIANDAYS UDF

Description

The DATE_TO_JULIANDAYS_UDF() function takes a DATE and returns the number of days since January 1, 4712 BC. This function is equivalent to the Oracle TO_CHAR(DATE,'J')

Custom UDF overloads

DATE_TO_JULIANDAYS_UDF(date)

Parameters

1. FIRST_PARAM: The DATE of the operation.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_TO_JULIAN_DAYS_UDF(input_date DATE)
RETURNS NUMBER  
IMMUTABLE
AS
$$
    DATEDIFF(DAY,TO_DATE('00000101','YYYYMMDD'),TO_DATE('01/01/4712','DD/MM/YYYY')) +
    DATEDIFF(DAY,TO_DATE('00000101','YYYYMMDD'),input_date) + 38 
    // Note: The 38 on the equation marks the differences in days between calendars and must be updated on the year 2099
$$
;

Usage Example

Oracle

Query

--Create Table
CREATE TABLE datetojulian_table (col1 DATE);

INSERT INTO datetojulian_table VALUES (DATE '2020-01-01');
INSERT INTO datetojulian_table VALUES (DATE '1900-12-31');
INSERT INTO datetojulian_table VALUES (DATE '1904-02-29');
INSERT INTO datetojulian_table VALUES (DATE '1903-03-01');
INSERT INTO datetojulian_table VALUES (DATE '2000-12-31');

--Select
SELECT TO_CHAR(col1, 'J') FROM datetojulian_table;

Snowflake

Query

--Create Table
CREATE OR REPLACE TABLE datetojulian_table (col1 TIMESTAMP /*** MSC-WARNING - MSCEWI3060 - DEFAULT VALUE FOR SYSDATE IS CURRENT_TIMESTAMP. COLUMN WAS TRANSFORMED TO TIMESTAMP TO PRESERVE INFORMATION. ***/
);

INSERT INTO datetojulian_table
VALUES (DATE '2020-01-01');

INSERT INTO datetojulian_table
VALUES (DATE '1900-12-31');

INSERT INTO datetojulian_table
VALUES (DATE '1904-02-29');

INSERT INTO datetojulian_table
VALUES (DATE '1903-03-01');

INSERT INTO datetojulian_table
VALUES (DATE '2000-12-31');

--Select
SELECT
--** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DATE_TO_JULIAN_DAYS_UDF' INSERTED. **
PUBLIC.DATE_TO_JULIAN_DAYS_UDF(
                               --** MSC-WARNING - MSCEWI3061 - COLUMN WAS TRANSFORMED FROM DATE TO TIMESTAMP.  SOME OPERATIONS MAY BE AFFECTED **
                               col1)
FROM
datetojulian_table;

Known Issues

No issues were found.

Related EWIs

1. MSCEWI1020: CUSTOM UDF INSERTED.