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. INPUT_DATE: The DATE of the operation.

UDF

CREATE OR REPLACE FUNCTION PUBLIC.DATE_TO_JULIAN_DAYS_UDF(input_date DATE)
RETURNS NUMBER  
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
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

IN -> Oracle_01.sql

--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

OUT -> Oracle_01.sql

--Create Table
CREATE OR REPLACE TABLE datetojulian_table (col1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

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
PUBLIC.DATE_TO_JULIAN_DAYS_UDF(col1)
FROM
datetojulian_table;

Known Issues

No issues were found.

Related EWIs

• SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior