UDFs personnalisées SnowConvert

Description

Certaines fonctions et fonctionnalités intégrées d’Oracle peuvent ne pas être disponibles ou se comporter différemment dans Snowflake. Pour minimiser ces différences, certaines fonctions sont remplacées par des UDFs personnalisées SnowConvert.

Ces UDFs sont automatiquement créées lors de la migration, dans le dossier UDF Helper, à l’intérieur du dossier Output. Il y a un fichier par UDF personnalisée.

BFILENAME UDF

Description

Cette fonction prend le nom du répertoire et les paramètres du nom de fichier de Oracle BFILENAME() comme STRING et renvoie une concaténation de ceux-ci à l’aide de \. Comme BFILE est traduit en VARCHAR, le résultat BFILENAME est traité comme du texte.

Avertissement

\ doit être modifié pour correspondre au caractère de concaténation du fichier correspondant du système d’opérateur.

Surcharges UDF personnalisées

BFILENAME_UDF(string, string)

Concatène le chemin du répertoire et le nom du fichier.

Paramètres

  1. DIRECTORYNAME : Une STRING qui représente le chemin du répertoire.

  2. FILENAME : Une STRING qui représente le nom du fichier.

      -- UDF
      CREATE OR REPLACE FUNCTION PUBLIC.BFILENAME_UDF (DIRECTORYNAME STRING, FILENAME STRING)
      RETURNS STRING
      LANGUAGE SQL
      IMMUTABLE 
      COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
      AS
      $$
      DIRECTORYNAME || '\\' || FILENAME
      $$;
Copy
Oracle
     --Create Table
     CREATE TABLE bfile_table ( col1 BFILE );
 
     --Insert Bfilename
     INSERT INTO bfile_table VALUES ( BFILENAME('mydirectory', 'myfile.png') );

     --Select
     SELECT * FROM bfile_table;
Copy
-- Result
COL1              |
------------------+
[BFILE:myfile.png]|
Copy
Snowflake
--Create Table
CREATE OR REPLACE TABLE bfile_table ( col1
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--Insert Bfilename
INSERT INTO bfile_table
VALUES (PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png') );

--Select
SELECT * FROM
bfile_table;
Copy
-- Result
COL1                  |
----------------------+
mydirectory\myfile.png|
Copy

Problèmes connus

1. No access to the DBMS_LOB built-in package

Les types de données LOB n’étant pas pris en charge dans Snowflake, il n’existe pas d’équivalent pour les fonctions DBMS_LOB et aucune solution de contournement n’a encore été mise en œuvre.

CAST_DATE UDF

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

Cette UDF personnalisée est ajoutée pour éviter les exceptions d’exécution causées par des différences de format lors de la conversion de chaînes en DATE, à l’intérieur de procédures et de fonctions.

Surcharges UDF personnalisées

CAST_DATE_UDF(datestr)

Crée une DATE à partir d’une STRING.

Paramètres

  1. DATESTR : Une STRING qui représente une DATE avec un format spécifique.

 CREATE OR REPLACE FUNCTION PUBLIC.CAST_DATE_UDF(DATESTR STRING) 
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	SELECT TO_DATE(DATESTR,'YYYY-MM-DD"T"HH24:MI:SS.FF')
$$;
Copy
Oracle
 :force: 

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

--Create Procedure
CREATE OR REPLACE PROCEDURE jsdateudf_proc ( par1 DATE )
IS
BEGIN
    INSERT INTO jsdateudf_table VALUES(par1);
END;

--Insert Date
CALL jsdateudf_proc('20-03-1996');

--Select
SELECT * FROM jsdateudf_table;
Copy
COL1                   |
-----------------------+
1996-03-20 00:00:00.000|
Copy
Snowflake
 --Create Table
CREATE OR REPLACE TABLE jsdateudf_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"}}'
;

--Create Procedure
CREATE OR REPLACE PROCEDURE jsdateudf_proc (par1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        INSERT INTO jsdateudf_table
        VALUES(:par1);
    END;
$$;

--Insert Date
CALL jsdateudf_proc('20-03-1996');

--Select
SELECT * FROM
    jsdateudf_table;
Copy
COL1      |
----------+
1996-03-20|
Copy

Problèmes connus

1. Oracle DATE contains TIMESTAMP

Tenez compte du fait que Oracle DATE contient un TIMESTAMP vide (00:00:00.000), alors que Snowflake DATE n’en contient pas. SnowConvert permet de transformer DATE en TIMESTAMP avec l’indicateur SysdateAsCurrentTimestamp.

EWIs connexes

  1. SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent

DATE_TO_JULIANDAYS_UDF

Description

La fonction DATE_TO_JULIANDAYS_UDF() prend une DATE et renvoie le nombre de jours depuis le 1er janvier, 4712 BC. Cette fonction est équivalente à Oracle TO_CHAR(DATE, “J”)

Surcharges UDF personnalisées

DATE_TO_JULIANDAYS_UDF(date)

Paramètres

  1. INPUT\DATE : DATE de l’opération.

 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
$$
;
Copy

Exemple d’utilisation

Oracle
 --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;
Copy
Snowflake
 --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;
Copy

Problèmes connus

Aucun problème n’a été constaté.

EWIs connexes

  • SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent

DATEADD UDF

Description

Cette UDF est utilisée comme modèle pour tous les cas où il y a un ajout entre un type DATE ou TIMESTAMP et un type FLOAT.

Surcharges UDF personnalisées

DATEADD_UDF(date, float)

Paramètres

  1. FIRST\PARAM : Première DATE de l’opération.

  2. SECOND\PARAM : Valeur FLOAT à ajouter.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM DATE, SECOND_PARAM FLOAT)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT FIRST_PARAM + SECOND_PARAM::NUMBER
$$;
Copy

DATEADD_UDF(float, date)

Paramètres

  1. FIRST\PARAM : Valeur FLOAT à ajouter.

  2. SECOND\PARAM : DATE de l’opération.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT FIRST_PARAM::NUMBER + SECOND_PARAM
$$;
Copy

DATEADD_UDF(timestamp, float)

Paramètres

  1. FIRST\PARAM : Premier TIMESTAMP de l’opération.

  2. SECOND\PARAM : Valeur FLOAT à ajouter.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM FLOAT)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT DATEADD(day, SECOND_PARAM,FIRST_PARAM)
$$;
Copy

DATEADD_UDF(float, timestamp)

Paramètres

  1. FIRST\PARAM : FLOAT de l’opération.

  2. SECOND\PARAM : TIMESTAMP de l’opération.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT DATEADD(day, FIRST_PARAM,SECOND_PARAM)
$$;
Copy

Exemple d’utilisation

Oracle
 SELECT
    TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM')+62.40750856543442
FROM DUAL;
Copy
|TO_TIMESTAMP('03/08/2009,12:47AM','DD/MM/YY,HH:MIAM')+62.40750856543442|
|-----------------------------------------------------------------------|
|2009-10-04 10:33:49.000                                                |

Copy
Snowflake
 SELECT
    PUBLIC.DATEADD_UDF(TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM'), 62.40750856543442)
FROM DUAL;
Copy
|PUBLIC.DATEADD_UDF(
TO_TIMESTAMP('03/08/2009, 12:47 AM', 'DD/MM/YY, HH12:MI AM'), 62.40750856543442)|
|-----------------------------------------------------------------------------------------------------|
|2009-10-04 00:47:00.000                                                                              |

Copy

Problèmes connus

1. Differences in time precision

Lorsqu’il existe des opérations entre des dates ou des horodatages et des valeurs flottantes, l’heure peut différer de celle d’Oracle. Une action a été lancée pour corriger ce problème.

EWIs connexes

Pas d’EWIs connexes.

DATEDIFF UDF

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

Cette UDF est utilisée comme modèle dans tous les cas où il y a soustraction entre DATE, TIMESTAMP, et tout autre type (sauf Intervalles).

Surcharges UDF personnalisées

DATEDIFF_UDF(date, date)

Paramètres

  1. FIRST\PARAM : Première DATE de l’opération.

  2. SECOND_PARAM : DATE à soustraire.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	FIRST_PARAM - SECOND_PARAM
$$;
Copy

DATEDIFF_UDF(date, timestamp)

Paramètres

  1. FIRST\PARAM : Première DATE de l’opération.

  2. SECOND_PARAM : TIMESTAMP à soustraire.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	FIRST_PARAM - SECOND_PARAM::DATE
$$;
Copy

DATEDIFF_UDF(date, integer)

Paramètres

  1. FIRST\PARAM : Première DATE de l’opération.

  2. SECOND_PARAM : INTEGER à soustraire.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM INTEGER)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEADD(day,SECOND_PARAM*-1 ,FIRST_PARAM)
$$;
Copy

DATEDIFF_UDF(timestamp, timestamp)

Paramètres

  1. FIRST\PARAM : Premier TIMESTAMP de l’opération.

  2. SECOND_PARAM : TIMESTAMP à soustraire.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;
Copy

DATEDIFF_UDF(timestamp, date)

Paramètres

  1. FIRST\PARAM : Premier TIMESTAMP de l’opération.

  2. SECOND_PARAM : DATE à soustraire.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;
Copy

DATEDIFF_UDF(timestamp, number)

Paramètres

  1. FIRST\PARAM : Premier TIMESTAMP de l’opération.

  2. SECOND_PARAM : NUMBER à soustraire.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM NUMBER)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEADD(day,SECOND_PARAM*-1,FIRST_PARAM)
$$;
Copy

Exemple d’utilisation

Note

L’inconnu est une colonne dont le type n’a pas pu être résolu, il peut s’agir d’un horodatage, d’une date entière ou d’un nombre.

Note

--disableDateAsTimestamp

Indicateur spécifiant si SYSDATE doit être transformé en CURRENT_DATE ou CURRENT_TIMESTAMP. Cela affectera également toutes les colonnes DATE qui seront transformées en TIMESTAMP.

Oracle
 --Create Table
CREATE TABLE times(AsTimeStamp TIMESTAMP, AsDate DATE);

--Subtraction operations
SELECT AsDate - unknown FROM times, unknown_table;
SELECT unknown - AsTimeStamp FROM times;
SELECT AsTimeStamp - unknown FROM times;
SELECT unknown - AsDate FROM times;
Copy
Snowflake
 --Create Table
CREATE OR REPLACE TABLE times (AsTimeStamp TIMESTAMP(6),
AsDate 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"}}'
;

--Subtraction operations
SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN DATE AND unknown ***/!!!
 AsDate, unknown) FROM
times,
unknown_table;

SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Timestamp ***/!!!
 unknown, AsTimeStamp) FROM
times;

SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/!!!
 AsTimeStamp, unknown) FROM
times;

SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND DATE ***/!!!
 unknown, AsDate) FROM
times;
Copy

Problèmes connus

1. Functional differences for timestamps

Il arrive que la valeur de Snowflake renvoyée par l’UDF diffère de celle d’Oracle en raison de l’heure. Considérez l’exemple suivant

Oracle
 -- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));

CREATE TABLE TIMES(AsTimeStamp TIMESTAMP);
INSERT INTO TIMES VALUES (TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'));

SELECT AsTimeStamp - unknown FROM times, unknown_table;
Copy
|ASTIMESTAMP-UNKNOWN|
|-------------------|
|4417 23:0:0.0      |
Copy
Snowflake
 -- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));
CREATE OR REPLACE TABLE TIMES (AsTimeStamp TIMESTAMP(6)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO TIMES
VALUES (TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'));

SELECT
PUBLIC.DATEDIFF_UDF(
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/!!!
    AsTimeStamp,
    unknown
)
FROM
times,
unknown_table;
Copy
PUBLIC.DATEDIFF_UDF( ASTIMESTAMP, UNKNOWN)|
------------------------------------------+
                                      4418|
Copy

EWIs connexes

  1. SSC-EWI-OR0036 : Problèmes de résolution de types, l’opération arithmétique peut ne pas se comporter correctement entre une chaîne et une date.

  2. SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent.

JSON_VALUE UDF

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

Conformément à la documentation d’Oracle, cette fonction utilise l’expression de chemin SQL/JSON pour requérir des informations sur une partie d’une instance JSON. La valeur de retour est toujours une valeur scalaire, sinon la fonction renvoie NULL par défaut.

JSON_VALUE
  ( expr [ FORMAT JSON ], [ JSON_basic_path_expression ]
    [ JSON_value_returning_clause ] [ JSON_value_on_error_clause ]
    [ JSON_value_on_empty_clause ][ JSON_value_on_mismatch_clause ]
  )
Copy

Le site JSON_VALUE_UDF est une implémentation Snowflake de la spécification JSONPath qui utilise une version modifiée de l’implémentation originale JavaScript développée par Stefan Goessner.

Modèles d’échantillons de sources

Données de configuration

Exécutez ces requêtes pour exécuter les requêtes de la section Modèles JSON_VALUE.

Oracle
 CREATE TABLE MY_TAB (
    my_json VARCHAR(5000)
);

INSERT INTO MY_TAB VALUES ('{ 
    "store": {
      "book": [ 
        { "category": "reference",
          "author": "Nigel Rees",
          "title": "Sayings of the Century",
          "price": 8.95
        },
        { "category": "fiction",
          "author": "Evelyn Waugh",
          "title": "Sword of Honour",
          "price": 12.99
        },
        { "category": "fiction",
          "author": "Herman Melville",
          "title": "Moby Dick",
          "isbn": "0-553-21311-3",
          "price": 8.99
        },
        { "category": "fiction",
          "author": "J. R. R. Tolkien",
          "title": "The Lord of the Rings",
          "isbn": "0-395-19395-8",
          "price": 22.99
        }
      ],
      "bicycle": {
        "color": "red",
        "price": 19.95
      }
    }
  }');
Copy
Snowflake
 CREATE OR REPLACE TABLE MY_TAB (
       my_json VARCHAR(5000)
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

   INSERT INTO MY_TAB
   VALUES ('{ 
    "store": {
      "book": [ 
        { "category": "reference",
          "author": "Nigel Rees",
          "title": "Sayings of the Century",
          "price": 8.95
        },
        { "category": "fiction",
          "author": "Evelyn Waugh",
          "title": "Sword of Honour",
          "price": 12.99
        },
        { "category": "fiction",
          "author": "Herman Melville",
          "title": "Moby Dick",
          "isbn": "0-553-21311-3",
          "price": 8.99
        },
        { "category": "fiction",
          "author": "J. R. R. Tolkien",
          "title": "The Lord of the Rings",
          "isbn": "0-395-19395-8",
          "price": 22.99
        }
      ],
      "bicycle": {
        "color": "red",
        "price": 19.95
      }
    }
  }');
Copy

Modèles JSON_VALUE

Oracle
 -- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title') AS VALUE FROM MY_TAB;

-- NULL
-- gets books in positions 0, 1, 2 and 3 but returns null (default behavior) since a non scalar value was returned
SELECT JSON_VALUE(MY_JSON, '$..book[0,1 to 3,3]') AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$.store.book[*]?(@.category == "reference").title') AS VALUE FROM MY_TAB;

-- 'MY ERROR MESSAGE'
-- triggers error because the result is a non scalar value (is an object)
SELECT JSON_VALUE(MY_JSON, '$..book[0]' DEFAULT 'MY ERROR MESSAGE' ON ERROR DEFAULT 'MY EMPTY MESSAGE' ON EMPTY) AS VALUE FROM MY_TAB;

-- 'MY EMPTY MESSAGE'
-- triggers the on empty class because does not exists in the first book element
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' DEFAULT 'MY ERROR MESSAGE' ON ERROR DEFAULT 'MY EMPTY MESSAGE' ON EMPTY) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON ERROR clause is set to ERROR
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' ERROR ON ERROR) AS VALUE FROM MY_TAB;

-- NULL
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' NULL ON ERROR) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON EMPTY clause is set to ERROR
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' ERROR ON EMPTY) AS VALUE FROM MY_TAB;

-- NULL
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' NULL ON EMPTY) AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING VARCHAR2) AS VALUE FROM MY_TAB;

-- 'Sayin'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING VARCHAR2(5) TRUNCATE) AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING CLOB) AS VALUE FROM MY_TAB;

-- NULL
-- This is because the title field is a string and the function expects a number result type
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING NUMBER) AS VALUE FROM MY_TAB;

-- 420
-- This is because the title field is a string and the function expects a number result type
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING NUMBER DEFAULT 420 ON ERROR) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-01858: a non-numeric character was found where a numeric was expected
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING DATE ERROR ON ERROR) AS VALUE FROM MY_TAB;

-- ORA-40450: invalid ON ERROR clause
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' ERROR ON MISMATCH) AS VALUE FROM MY_TAB;
Copy
JSON PathQuery result
'$..book[0].title''Sayings of the Century'
'$..book[0,1 to 3,3]'NULL
'$.store.book[*]?(@.category == "reference").title''Sayings of the Century'
'$..book[0]''MY ERROR MESSAGE'
'$..book[0].isbn''MY EMPTY MESSAGE'
'$..book[0].isbn'ORA-40462: JSON_VALUE evaluated to no value
'$..book[0].isbn'NULL
'$..book[0].isbn'ORA-40462: JSON_VALUE evaluated to no value
'$..book[0].isbn'NULL
'$..book[0].title''Sayings of the Century'
'$..book[0].title''Sayin'
'$..book[0].title''Sayings of the Century'
'$..book[0].title'NULL
'$..book[0].title'420
'$..book[0].title'ORA-01858: a non-numeric character was found where a numeric was expected
'$..book[0].title'ORA-40450: invalid ON ERROR clause
Snowflake
-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- NULL
-- gets books in positions 0, 1, 2 and 3 but returns null (default behavior) since a non scalar value was returned
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0,1 to 3,3]', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$.store.book[*]?(@.category == "reference").title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'MY ERROR MESSAGE'
-- triggers error because the result is a non scalar value (is an object)
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0]', NULL, 'MY ERROR MESSAGE' :: VARIANT, 'MY EMPTY MESSAGE' :: VARIANT) AS VALUE FROM
MY_TAB;

-- 'MY EMPTY MESSAGE'
-- triggers the on empty class because does not exists in the first book element
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'MY ERROR MESSAGE' :: VARIANT, 'MY EMPTY MESSAGE' :: VARIANT) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON ERROR clause is set to ERROR
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'SSC_ERROR_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- NULL
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'SSC_NULL_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON EMPTY clause is set to ERROR
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, NULL, 'SSC_ERROR_ON_EMPTY' :: VARIANT) AS VALUE FROM
MY_TAB;

-- NULL
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, NULL, 'SSC_NULL_ON_EMPTY' :: VARIANT) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'Sayin'
SELECT
LEFT(JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL), 5) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL) AS VALUE FROM
MY_TAB;

-- NULL
-- This is because the title field is a string and the function expects a number result type
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'number', NULL, NULL) AS VALUE FROM
MY_TAB;

-- 420
-- This is because the title field is a string and the function expects a number result type
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'number', 420 :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-01858: a non-numeric character was found where a numeric was expected
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - RETURNING CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, 'SSC_ERROR_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- ORA-40450: invalid ON ERROR clause
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - ON MISMATCH CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;
Copy
JSON PathQuery result
'$..book[0].title''Sayings of the Century'
'$..book[0,1 to 3,3]'NULL
'$.store.book[*]?(@.category == "reference").title''Sayings of the Century'
'$..book[0]''MY ERROR MESSAGE'
'$..book[0].isbn''MY EMPTY MESSAGE'
'$..book[0].isbn'"SSC_CUSTOM_ERROR - NO MATCH FOUND"
'$..book[0].isbn'NULL
'$..book[0].isbn'"SSC_CUSTOM_ERROR - NO MATCH FOUND"
'$..book[0].isbn'NULL
'$..book[0].title''Sayings of the Century'
'$..book[0].title''Sayin'
'$..book[0].title''Sayings of the Century'
'$..book[0].title'NULL
'$..book[0].title'420
'$..book[0].title'NOT SUPPORTED
'$..book[0].title'NOT SUPPORTED

Problèmes connus

1. Returning Type Clause is not fully supported

Désormais, les seuls types pris en charge lors de la traduction de la fonctionnalité de la clause RETURNING TYPE sont VARCHAR2, CLOB et NUMBER.

Pour tous les autres types pris en charge par la fonction originale JSON_VALUE, la fonction JSON_VALUE_UDF se comportera comme si aucune clause RETURNING TYPE n’avait été spécifiée.

Types non pris en charge :

  • DATE

  • TIMESTAMP [WITH TIME ZONE]

  • SDO_GEOMETRY

  • CUSTOM TYPE

2. ON MISMATCH Clause is not supported

Désormais, la clause ON MISMATCH n’est pas prise en charge et un avertissement EWI est placé à la place. Ainsi, le code traduit se comportera comme si aucune clause ON MISMATCH n’avait été spécifiée à l’origine.

3. Complex filters are not supported

Les filtres complexes comportant plus d’une expression renverront un résultat null, car ils ne sont pas pris en charge.

Par exemple, avec les mêmes données que précédemment, ce chemin JSON $.store.book[*]?(@.category == "reference").title est pris en charge et renverra 'Sayings of the Century'.

Cependant, $.store.book[*]?(@.category == "reference" && @.price < 10).title renverra null puisque plus d’une expression est utilisée dans le filtre.

EWIs connexes

  1. SSC-EWI-0021 : Non pris en charge dans Snowflake.

JULIAN TO GREGORIAN DATE UDF

Description

Cette fonction définie par l’utilisateur (UDF) est utilisée pour transformer ou convertir le format de date julien en format de date grégorien. Les dates juliennes peuvent être reçues dans trois formats différents tels que JD Edwards World, astronomie ou le format ordinaire.

Surcharges UDF personnalisées

JULIAN_TO_GREGORIAN_DATE_UDF(julianDate, formatSelected)

Renvoie une chaîne avec le format de date grégorien YYYY-MM-DD.

Paramètres :

JulianDate : La date julienne qui doit être transmise. Il peut s’agir de CYYDDD (où C est le siècle) ou de YYYYDDD.

formatSelected : Représente le format dans lequel la date julienne doit être traitée. En outre, il s’agit d’un CHAR qui peut accepter les formats suivants :

Format availableLetter representation in CHARDescription
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.
 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;
    }
    
$$
;
Copy

Exemple d’utilisation

Oracle

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

| TO\_DATE('2020001', 'J') |
| ------------------------ |
| 18-JUN-18                |



| TO\_CHAR(TO\_DATE('2020001', 'J'), 'YYYY-MON-DD') |
| ------------------------------------------------- |
| 0818-JUN-18                                       |
Copy
  • _Remarque : La date doit être formatée afin de visualiser tous les chiffres de l’année

Snowflake

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

| JULIAN\_TO\_GREGORIAN\_DATE\_UDF('2020001', 'J') |
| ------------------------------------------------ |
| "0818-06-18"                                     |
Copy

Problèmes connus

  1. Tout autre format : Si la date julienne est formatée dans un autre format non pris en charge, il y aura des différences dans la sortie.

  2. Les plages de dates de B.C. peuvent présenter des incohérences dues à des fonctions Snowflake non prises en charge pour les dates.

EWIs connexes

Pas d’EWIs connexes.

MONTHS BETWEEN UDF [DEPRECATED]

Cette UDF a été supprimée. La fonction actuelle pour Oracle MONTHS_BETWEEN() est Snowflake MONTHS_BETWEEN().

Description

MONTHS_BETWEEN renvoie le nombre de mois entre les dates date1 and date2. (Oracle MONTHS_BETWEEN Référence linguistique SQL)

MONTHS_BETWEEN(date1, date2)
Copy

Les fonctions Oracle MONTHS_BETWEEN et Snowflake MONTHS_BETWEEN présentent quelques différences fonctionnelles. Afin de minimiser ces différences et de mieux répliquer la fonction Oracle MONTHS_BETWEEN, nous avons ajouté une UDF personnalisée.

Custom UDF overloads

MONTHS_BETWEEN_UDF(timestamp_ltz, timestamp_ltz)

Paramètres

  1. FIRST\DATE : Premier TIMESTAMP_LTZ de l’opération.

  2. SECOND\DATE : Le deuxième TIMESTAMP_LTZ de l’opération.

 CREATE OR REPLACE FUNCTION MONTHS_BETWEEN_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)
RETURNS NUMBER
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
ROUND(MONTHS_BETWEEN(FIRST_DATE, SECOND_DATE))
$$
;
Copy
Oracle
 SELECT 
	MONTHS_BETWEEN('2000-03-20 22:01:11', '1996-03-20 10:01:11'),
	MONTHS_BETWEEN('1996-03-20 22:01:11', '2000-03-20 10:01:11'),
	MONTHS_BETWEEN('1982-05-11 22:31:19', '1900-01-25 15:21:15'),
	MONTHS_BETWEEN('1999-12-25 01:15:16', '1900-12-11 02:05:16')
FROM DUAL;
Copy
MONTHS_BETWEEN('2000-03-2022:01:11','1996-03-2010:01:11')|MONTHS_BETWEEN('1996-03-2022:01:11','2000-03-2010:01:11')|MONTHS_BETWEEN('1982-05-1122:31:19','1900-01-2515:21:15')|MONTHS_BETWEEN('1999-12-2501:15:16','1900-12-1102:05:16')|
---------------------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+
                                                       48|                                                      -48|                 987.558021206690561529271206690561529271|                1188.450492831541218637992831541218637993|
Copy
Snowflake
 SELECT
	MONTHS_BETWEEN('2000-03-20 22:01:11', '1996-03-20 10:01:11'),
	MONTHS_BETWEEN('1996-03-20 22:01:11', '2000-03-20 10:01:11'),
	MONTHS_BETWEEN('1982-05-11 22:31:19', '1900-01-25 15:21:15'),
	MONTHS_BETWEEN('1999-12-25 01:15:16', '1900-12-11 02:05:16')
FROM DUAL;
Copy
MONTHS_BETWEEN_UDF('2000-03-20 22:01:11', '1996-03-20 10:01:11')|MONTHS_BETWEEN_UDF('1996-03-20 22:01:11', '2000-03-20 10:01:11')|MONTHS_BETWEEN_UDF('1982-05-11 22:31:19', '1900-01-25 15:21:15')|MONTHS_BETWEEN_UDF('1999-12-25 01:15:16', '1900-12-11 02:05:16')|
----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+
                                                       48.000000|                                                      -48.000000|                                                      987.558024|                                                     1188.450497|
Copy

Known Issues

1. Precision may differ from Oracle

Certains résultats peuvent différer quant au nombre de chiffres décimaux.

Related EWIs

Pas d’EWIs connexes.

REGEXP LIKE UDF

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

REGEXP_LIKE permet de faire correspondre des expressions régulières. Cette condition évalue les chaînes à l’aide des caractères définis par l’ensemble de caractères d’entrée.(Oracle Language Reference REGEXP_LIKE Condition)

 REGEXP_LIKE(source_char, pattern [, match_param ])
Copy

Oracle REGEXP_LIKE et Snowflake REGEXP_LIKE présentent quelques différences de fonctionnalité. Pour minimiser ces différences et mieux répliquer la fonction Oracle REGEXP_LIKE, nous avons ajouté une UDF personnalisée. L’idée principale est d’échapper le symbole de la barre oblique inverse dans l’expression régulière lorsqu’il est exigé. Voici les caractères spéciaux qui doivent être échappés lorsqu’ils sont accompagnés d’une barre oblique inverse : 'd', 'D', 'w', 'W', 's', 'S', 'A', 'Z', 'n'. En outre, l’expression de rétro-référence (correspond au même texte que celui qui a été trouvé le plus récemment par le groupe de capture « numéro spécifié ») doit être échappée.

Surcharges UDF personnalisées

REGEXP_LIKE_UDF(string, string)

Paramètres
  1. COL : est l’expression de caractère qui sert de valeur de recherche.

  2. PATTERN : est l’expression régulière.

 CREATE OR REPLACE FUNCTION REGEXP_LIKE_UDF(COL STRING, PATTERN STRING) 
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
return COL.match(new RegExp(PATTERN));
$$;
Copy
Oracle
Snowflake

REGEXP_LIKE_UDF(string, string, string)

Paramètres
  1. COL : est l’expression de caractère qui sert de valeur de recherche.

  2. PATTERN : est l’expression régulière.

  3. MATCHPARAM : est une expression de caractère qui permet de modifier le comportement par défaut de la condition. Dans la table suivante, vous trouverez les caractères Oracle avec leur description et leur équivalent dans l’UDF.

Match ParameterDescriptionUDF Equivalent
'i'Specifies case-insensitive matching, even if the determined collation of the condition is case-sensitive.'i'
'c'Specifies case-sensitive and accent-sensitive matching, even if the determined collation of the condition is case-insensitive or accent-insensitive.Does not have an equivalent. It is being removed from the parameter..
'n'Allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, then the period does not match the newline character.'s'
'm'Treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then Oracle treats the source string as a single line.'m'
'x'Ignores whitespace characters. By default, whitespace characters match themselves.Does not have an equivalent. It is being removed from the parameter.
 CREATE OR REPLACE FUNCTION REGEXP_LIKE_UDF(COL STRING, PATTERN STRING, MATCHPARAM STRING) 
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
return COL.match(new RegExp(PATTERN, MATCHPARAM));
$$;
Copy
Oracle
Snowflake

Problèmes connus

1. UDF match parameter may not behave as expected

Tous les caractères disponibles dans le paramètre de correspondance d’Oracle n’ayant pas leur équivalent dans la fonction définie par l’utilisateur, le résultat de la requête peut présenter certaines différences fonctionnelles par rapport à Oracle.

2. UDF pattern parameter does not allow only “\” as a regular expression

Si le paramètre du modèle de l’expression régulière utilisée est uniquement « \ », une exception sera levée comme suit : JavaScript execution error: Uncaught SyntaxError: Invalid regular expression: //: \ at end of pattern in REGEXP_LIKE_UDF at “return COL.match(new RegExp(PATTERN));” position 17 stackstrace: REGEXP_LIKE_UDF

TIMESTAMP DIFF UDF

Description

Snowflake ne prend pas en charge l’opération d’addition entre les types de données TIMESTAMP avec l’opérateur -. Afin de répliquer cette fonctionnalité, nous avons ajouté une UDF personnalisée.

Surcharges UDF personnalisées

TIMESTAMP_DIFF_UDF(timestamp, timestamp)

Paramètres

  1. LEFT\TS : Premier TIMESTAMP de l’opération.

  2. RIGHT\TS : Valeur TIMESTAMP à ajouter.

 CREATE OR REPLACE FUNCTION TIMESTAMP_DIFF_UDF(LEFT_TS TIMESTAMP, RIGHT_TS TIMESTAMP )
RETURNS VARCHAR
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH RESULTS(days,hours,min,sec,millisecond,sign) AS
(
  SELECT
  abs(TRUNC(x/1000/3600/24)) days,
  abs(TRUNC(x/1000/60 / 60)-trunc(x/1000/3600/24)*24) hours,
  abs(TRUNC(MOD(x/1000,3600)/60)) min,
  abs(TRUNC(MOD(x/1000,60))) sec,
  abs(TRUNC(MOD(x,1000))) millisecond,
  SIGN(x)
  FROM (SELECT TIMESTAMPDIFF(millisecond, RIGHT_TS, LEFT_TS) x ,SIGN(TIMESTAMPDIFF(millisecond, RIGHT_TS, LEFT_TS)) sign))
  SELECT
  IFF(SIGN>0,'+','-') || TRIM(TO_CHAR(days,'000000000')) || ' ' || TO_CHAR(hours,'00') || ':' || TRIM(TO_CHAR(min,'00')) || ':' || TRIM(TO_CHAR(sec,'00')) || '.' || TRIM(TO_CHAR(millisecond,'00000000'))
  from RESULTS
$$;
Copy
Oracle
 --Create Table
CREATE TABLE timestampdiff_table (col1 TIMESTAMP, col2 TIMESTAMP);

--Insert data
INSERT INTO timestampdiff_table VALUES ('2000-03-20 22:01:11', '1996-03-20 10:01:11');
INSERT INTO timestampdiff_table VALUES ('1996-03-20 22:01:11', '2000-03-20 10:01:11');
INSERT INTO timestampdiff_table VALUES ('1982-05-11 22:31:19', '1900-01-25 15:21:15');
INSERT INTO timestampdiff_table VALUES ('1999-12-25 01:15:16', '1900-12-11 02:05:16');

--Select
SELECT col1 - col2 FROM timestampdiff_table;
Copy
COL1-COL2      |
---------------+
1461 12:0:0.0  |
-1460 12:0:0.0 |
30056 7:10:4.0 |
36172 23:10:0.0|
Copy
Snowflake
 --Create Table
CREATE OR REPLACE TABLE timestampdiff_table (col1 TIMESTAMP(6),
col2 TIMESTAMP(6)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--Insert data
INSERT INTO timestampdiff_table
VALUES ('2000-03-20 22:01:11', '1996-03-20 10:01:11');

INSERT INTO timestampdiff_table
VALUES ('1996-03-20 22:01:11', '2000-03-20 10:01:11');

INSERT INTO timestampdiff_table
VALUES ('1982-05-11 22:31:19', '1900-01-25 15:21:15');

INSERT INTO timestampdiff_table
VALUES ('1999-12-25 01:15:16', '1900-12-11 02:05:16');

--Select
SELECT
PUBLIC.TIMESTAMP_DIFF_UDF( col1, col2) FROM
timestampdiff_table;
Copy
TIMESTAMP_DIFF_UDF( COL1, COL2)|
-------------------------------+
+000001461  12:00:00.00000000  |
-000001460  12:00:00.00000000  |
+000030056  07:10:04.00000000  |
+000036172  23:10:00.00000000  |
Copy

Problèmes connus

1. TIMESTAMP format may differ from Oracle

Le format TIMESTAMP peut différer de celui d’Oracle, veuillez tenir compte du paramètre TIMESTAMP_OUTPUT_FORMAT lorsque vous travaillez avec des types de données TIMESTAMP.

EWIs connexes

Pas d’EWIs connexes.

TRUNC (date) UDF

Description

La fonction TRUNC (date) renvoie la date `` avec l’heure tronquée à l’unité spécifiée par le modèle de format fmt. (Oracle TRUNC(date) Référence linguistique SQL)

TRUNC(date [, fmt ])
Copy

Les fonctions Oracle TRUNC et Snowflake TRUNC avec des arguments de type date présentent quelques différences fonctionnelles.

L’assistant TRUNC_UDF sera ajouté pour traiter les cas suivants :

1. Le format n’est pas pris en charge par Snowflake.

2. Le format existe dans Snowflake mais fonctionne différemment.

3. L’outil ne peut pas déterminer le type de données du premier argument.

4. Le format est fourni sous la forme d’une colonne ou d’une expression et non d’un littéral.

Surcharges UDF personnalisées

TRUNC\UDF(date)

Applique une conversion DATE explicite à l’horodatage d’entrée.

Paramètres

  1. INPUT : L’horodatage avec le fuseau horaire (TIMESTAMP_LTZ) qui doit être tronqué.

Avertissement

Le paramètre par défaut de l’UDF est TIMESTAMP_LTZ. Il peut être nécessaire de le remplacer par TIMESTAMP_TZ ou TIMESTAMP_NTZ pour correspondre à la valeur par défaut TIMESTAMP utilisée par l’utilisateur.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT TIMESTAMP_LTZ)
RETURNS DATE
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    INPUT::DATE
$$;
Copy
Oracle
 SELECT
TRUNC(
	TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' )
	)
"Date" FROM DUAL;
Copy
Date                   |
-----------------------+
1996-03-20 00:00:00.000|
Copy
Snowflake
 SELECT
TRUNC(
	TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' ), 'DD'
	)
"Date" FROM DUAL;
Copy
DATE      |
----------+
1996-03-20|
Copy

TRUNC\UDF(date, fmt)

Crée manuellement une nouvelle date à l’aide de la fonction DATE_FROM_PARTS(), en fonction de la catégorie de format utilisée.

Paramètres

  1. DATE_TO_TRUNC : L’horodatage avec le fuseau horaire (TIMESTAMP_LTZ) qui doit être tronqué.

  2. DATE_FMT : Le format de la date en tant que VARCHAR. Les mêmes formats que ceux pris en charge par Oracle.

Avertissement

Le paramètre par défaut de l’UDF est TIMESTAMP_LTZ. Il peut être nécessaire de le remplacer par TIMESTAMP_TZ ou TIMESTAMP_NTZ pour correspondre à la valeur par défaut TIMESTAMP utilisée par l’utilisateur.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(DATE_TO_TRUNC TIMESTAMP_LTZ, DATE_FMT VARCHAR(5))
RETURNS DATE
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
CAST(CASE 
WHEN UPPER(DATE_FMT) IN ('CC','SCC') THEN DATE_FROM_PARTS(CAST(LEFT(CAST(YEAR(DATE_TO_TRUNC) as CHAR(4)),2) || '01' as INTEGER),1,1)
WHEN UPPER(DATE_FMT) IN ('SYYYY','YYYY','YEAR','SYEAR','YYY','YY','Y') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)
WHEN UPPER(DATE_FMT) IN ('IYYY','IYY','IY','I') THEN 
    CASE DAYOFWEEK(DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 0 THEN DATEADD(DAY, 1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 1 THEN DATEADD(DAY, 0, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 2 THEN DATEADD(DAY, -1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 3 THEN DATEADD(DAY, -2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 4 THEN DATEADD(DAY, -3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 5 THEN DATEADD(DAY, 3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 6 THEN DATEADD(DAY, 2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
    END        
WHEN UPPER(DATE_FMT) IN ('MONTH','MON','MM','RM') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),MONTH(DATE_TO_TRUNC),1)
WHEN UPPER(DATE_FMT)IN ('Q') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),(QUARTER(DATE_TO_TRUNC)-1)*3+1,1)
WHEN UPPER(DATE_FMT) IN ('WW') THEN DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1),DATE_TO_TRUNC),7), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('IW') THEN DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,(CASE DAYOFWEEK(DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 0 THEN DATEADD(DAY, 1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 1 THEN DATEADD(DAY, 0, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 2 THEN DATEADD(DAY, -1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 3 THEN DATEADD(DAY, -2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 4 THEN DATEADD(DAY, -3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 5 THEN DATEADD(DAY, 3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 6 THEN DATEADD(DAY, 2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                               END),      DATE_TO_TRUNC),7), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('W') THEN DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),MONTH(DATE_TO_TRUNC),1),DATE_TO_TRUNC),7), DATE_TO_TRUNC)                                                             
WHEN UPPER(DATE_FMT) IN ('DDD', 'DD','J') THEN DATE_TO_TRUNC
WHEN UPPER(DATE_FMT) IN ('DAY', 'DY','D') THEN DATEADD(DAY, 0-DAYOFWEEK(DATE_TO_TRUNC), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('HH', 'HH12','HH24') THEN DATE_TO_TRUNC
WHEN UPPER(DATE_FMT) IN ('MI') THEN DATE_TO_TRUNC
END AS DATE)
$$
;
Copy

Scénarios de format TRUNC

Avertissement

Le format des résultats dépend des formats de sortie de DateTime configurés pour la base de données.

1. Natively supported formats

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY') FROM DUAL UNION ALL 
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Y') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Q') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MONTH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MON') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MI') FROM DUAL;
Copy
+───────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'YYYY')"  |
+───────────────────────────────────────────────────────────────────────+
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 20-APR-22                                                             |
| 20-APR-22                                                             |
| 20-APR-22                                                             |
+───────────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Y') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Q') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MONTH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MON') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MI') FROM DUAL;
Copy
+─────────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY')"  |
+─────────────────────────────────────────────────────────────────────────+
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-20                                                              |
| 2022-04-20                                                              |
| 2022-04-20                                                              |
+─────────────────────────────────────────────────────────────────────────+
Copy

2. Formats mapped to another format

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS')) FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SYYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SYEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'RM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IW') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DDD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'J') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH12') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH24') FROM DUAL;
Copy
+────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'))"  |
+────────────────────────────────────────────────────────────────+
| 20-APR-22                                                      |
| 01-JAN-22                                                      |
| 01-JAN-22                                                      |
| 01-APR-22                                                      |
| 18-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
+────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'YYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'WK') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'D') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'HH') FROM DUAL;
Copy
+────────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD')"  |
+────────────────────────────────────────────────────────────────────────+
| 2022-04-20                                                             |
| 2022-01-01                                                             |
| 2022-01-01                                                             |
| 2022-04-01                                                             |
| 2022-04-18                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
+────────────────────────────────────────────────────────────────────────+
Copy

3. Day formats

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'D') FROM DUAL;
Copy
+──────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'DAY')"  |
+──────────────────────────────────────────────────────────────────────+
| 17-APR-22                                                            |
| 17-APR-22                                                            |
| 17-APR-22                                                            |
+──────────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'D') FROM DUAL;
Copy
+────────────────────────────────────────────────────────────────────────────+
| "TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY')"  |
+────────────────────────────────────────────────────────────────────────────+
| 2022-04-17                                                                 |
| 2022-04-17                                                                 |
| 2022-04-17                                                                 |
+────────────────────────────────────────────────────────────────────────────+
Copy

4. Unsupported formats

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SCC') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'I') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'WW') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'W') FROM DUAL;
Copy
+─────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'CC')"  |
+─────────────────────────────────────────────────────────────────────+
| 01-JAN-01                                                           |
| 01-JAN-01                                                           |
| 03-JAN-22                                                           |
| 03-JAN-22                                                           |
| 03-JAN-22                                                           |
| 16-APR-22                                                           |
| 15-APR-22                                                           |
+─────────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SCC') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IYYY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'I') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'WW') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'W') FROM DUAL;
Copy
+───────────────────────────────────────────────────────────────────────────+
| "TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC')"  |
+───────────────────────────────────────────────────────────────────────────+
| 2001-01-01                                                                |
| 2001-01-01                                                                |
| 2022-01-03                                                                |
| 2022-01-03                                                                |
| 2022-01-03                                                                |
| 2022-04-16                                                                |
| 2022-04-15                                                                |
+───────────────────────────────────────────────────────────────────────────+
Copy

Note

Lorsque la fonction TRUNC est utilisée avec un format non pris en charge ou un paramètre qui ne peut être traité par SnowConvert. Pour éviter tout problème, le format est remplacé par un format valide ou TRUNC_UDF est ajouté.

Problèmes connus

1. Oracle DATE contains TIMESTAMP

Tenez compte du fait que Oracle DATE contient un TIMESTAMP vide (00:00:00.000), alors que Snowflake DATE n’en contient pas. SnowConvert permet de transformer DATE en TIMESTAMP avec l’indicateur SysdateAsCurrentTimestamp.

EWIs connexes

Pas d’EWIs connexes.

TRUNC (number) UDF

Description

La fonction TRUNC (number) renvoie n1 tronqué à n2 décimales. Si n2 est omis, n1 est tronqué à 0 chiffre. n2 peut être négatif pour tronquer (rendre nul) n2 chiffres à gauche de la virgule décimale. (Oracle TRUNC(number) Référence linguistique SQL)

TRUNC(n1 [, n2 ])
Copy

TRUNC_UDF pour les valeurs numériques sera ajouté pour traiter les cas où la première colonne a un type de données non reconnu.

Exemple :

 SELECT TRUNC(column1) FROM DUAL;
Copy

Si la définition de column1 n’a pas été fournie à l’outil. Ensuite, TRUNC_UDF sera ajouté et, lors de l’exécution, la surcharge de TRUNC_UDF permettra de gérer les cas où il s’agit d’un type numérique ou d’un type de date.

Consultez la section TRUNC (DATE).

Les sections suivantes apportent la preuve que TRUNC_UDF traitera des valeurs parfaitement numériques.

Surcharges UDF personnalisées

TRUNC_UDF(n1)

Appelle la fonction TRUNC de Snowflake avec le numéro d’entrée. Cette surcharge permet de traiter les différents types de paramètres, au cas où les informations ne seraient pas disponibles lors de la migration.

Paramètres

  1. INPUT : NUMBER qui doit être tronqué.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER)
RETURNS INT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    TRUNC(INPUT)
$$;
Copy
Oracle
 --TRUNC(NUMBER)
SELECT
	TRUNC ( 1.000001 ),
	TRUNC ( 15.79 ),
	TRUNC ( -975.975 ),
	TRUNC ( 135.135 )
FROM DUAL;
Copy
TRUNC(1.000001)|TRUNC(15.79)|TRUNC(-975.975)|TRUNC(135.135)|
---------------+------------+---------------+--------------+
              1|          15|           -975|           135|
Copy
Snowflake
 --TRUNC(NUMBER)
SELECT
	TRUNC ( 1.000001 ),
	TRUNC ( 15.79 ),
	TRUNC ( -975.975 ),
	TRUNC ( 135.135 )
FROM DUAL;
Copy
TRUNC_UDF(1.000001)|TRUNC_UDF(15.79)|TRUNC_UDF(-975.975)|TRUNC_UDF(135.135)|
-------------------+----------------+-------------------+------------------+
                  1|              15|               -975|               135|
Copy

TRUNC_UDF(n1, n2)

Appelle la fonction TRUNC de Snowflake avec le nombre d’entrée et l’échelle. Cette surcharge permet de traiter les différents types de paramètres, au cas où les informations ne seraient pas disponibles lors de la migration.

Paramètres

  1. INPUT : NUMBER qui doit être tronqué.

  2. SCALE : Représente le nombre de chiffres que la sortie inclura après la virgule décimale.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER, SCALE NUMBER)
RETURNS INT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    TRUNC(INPUT, SCALE)
$$;
Copy
Oracle
 --TRUNC(NUMBER, SCALE)
SELECT
	TRUNC ( 1.000001, -2 ),
	TRUNC ( 1.000001, -1 ),
	TRUNC ( 1.000001, 0 ),
	TRUNC ( 1.000001, 1 ),
	TRUNC ( 1.000001, 2 ),
	TRUNC ( 15.79, -2),
	TRUNC ( 15.79, -1),
	TRUNC ( 15.79, 0),
	TRUNC ( 15.79, 1 ),
	TRUNC ( 15.79, 50 ),
	TRUNC ( -9.6, -2 ),
	TRUNC ( -9.6, -1 ),
	TRUNC ( -9.6, 0 ),
	TRUNC ( -9.6, 1 ),
	TRUNC ( -9.6, 2 ),
	TRUNC ( -975.975, -3 ),
	TRUNC ( -975.975, -2 ),
	TRUNC ( -975.975, -1 ),
	TRUNC ( -975.975, 0 ),
	TRUNC ( -975.975, 1 ),
	TRUNC ( -975.975, 2 ),
	TRUNC ( -975.975, 3 ),
	TRUNC ( -975.975, 5 ),
	TRUNC ( 135.135, -10 ),
	TRUNC ( 135.135, -2 ),
	TRUNC ( 135.135, 0 ),
	TRUNC ( 135.135, 1 ),
	TRUNC ( 135.135, 2 ),
	TRUNC ( 135.135, 3 ),
	TRUNC ( 135.135, 5 )
FROM DUAL;
Copy
TRUNC(1.000001,-2)|TRUNC(1.000001,-1)|TRUNC(1.000001,0)|TRUNC(1.000001,1)|TRUNC(1.000001,2)|TRUNC(15.79,-2)|TRUNC(15.79,-1)|TRUNC(15.79,0)|TRUNC(15.79,1)|TRUNC(15.79,50)|TRUNC(-9.6,-2)|TRUNC(-9.6,-1)|TRUNC(-9.6,0)|TRUNC(-9.6,1)|TRUNC(-9.6,2)|TRUNC(-975.975,-3)|TRUNC(-975.975,-2)|TRUNC(-975.975,-1)|TRUNC(-975.975,0)|TRUNC(-975.975,1)|TRUNC(-975.975,2)|TRUNC(-975.975,3)|TRUNC(-975.975,5)|TRUNC(135.135,-10)|TRUNC(135.135,-2)|TRUNC(135.135,0)|TRUNC(135.135,1)|TRUNC(135.135,2)|TRUNC(135.135,3)|TRUNC(135.135,5)|
------------------+------------------+-----------------+-----------------+-----------------+---------------+---------------+--------------+--------------+---------------+--------------+--------------+-------------+-------------+-------------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+------------------+-----------------+----------------+----------------+----------------+----------------+----------------+
                 0|                 0|                1|                1|                1|              0|             10|            15|          15.7|          15.79|             0|             0|           -9|         -9.6|         -9.6|                 0|              -900|              -970|             -975|           -975.9|          -975.97|         -975.975|         -975.975|                 0|              100|             135|           135.1|          135.13|         135.135|         135.135|
Copy
Snowflake
 --TRUNC(NUMBER, SCALE)
SELECT
	TRUNC ( 1.000001, -2 ),
	TRUNC ( 1.000001, -1 ),
	TRUNC ( 1.000001, 0 ),
	TRUNC ( 1.000001, 1 ),
	TRUNC ( 1.000001, 2 ),
	TRUNC ( 15.79, -2),
	TRUNC ( 15.79, -1),
	TRUNC ( 15.79, 0),
	TRUNC ( 15.79, 1 ),
	TRUNC ( 15.79, 50 ),
	TRUNC ( -9.6, -2 ),
	TRUNC ( -9.6, -1 ),
	TRUNC ( -9.6, 0 ),
	TRUNC ( -9.6, 1 ),
	TRUNC ( -9.6, 2 ),
	TRUNC ( -975.975, -3 ),
	TRUNC ( -975.975, -2 ),
	TRUNC ( -975.975, -1 ),
	TRUNC ( -975.975, 0 ),
	TRUNC ( -975.975, 1 ),
	TRUNC ( -975.975, 2 ),
	TRUNC ( -975.975, 3 ),
	TRUNC ( -975.975, 5 ),
	TRUNC ( 135.135, -10 ),
	TRUNC ( 135.135, -2 ),
	TRUNC ( 135.135, 0 ),
	TRUNC ( 135.135, 1 ),
	TRUNC ( 135.135, 2 ),
	TRUNC ( 135.135, 3 ),
	TRUNC ( 135.135, 5 )
FROM DUAL;
Copy
TRUNC_UDF ( 1.000001, -2 )|TRUNC_UDF ( 1.000001, -1 )|TRUNC_UDF ( 1.000001, 0 )|TRUNC_UDF ( 1.000001, 1 )|TRUNC_UDF ( 1.000001, 2 )|TRUNC_UDF ( 15.79, -2)|TRUNC_UDF ( 15.79, -1)|TRUNC_UDF ( 15.79, 0)|TRUNC_UDF ( 15.79, 1 )|TRUNC_UDF ( 15.79, 50 )|TRUNC_UDF ( -9.6, -2 )|TRUNC_UDF ( -9.6, -1 )|TRUNC_UDF ( -9.6, 0 )|TRUNC_UDF ( -9.6, 1 )|TRUNC_UDF ( -9.6, 2 )|TRUNC_UDF ( -975.975, -3 )|TRUNC_UDF ( -975.975, -2 )|TRUNC_UDF ( -975.975, -1 )|TRUNC_UDF ( -975.975, 0 )|TRUNC_UDF ( -975.975, 1 )|TRUNC_UDF ( -975.975, 2 )|TRUNC_UDF ( -975.975, 3 )|TRUNC_UDF ( -975.975, 5 )|TRUNC_UDF ( 135.135, -10 )|TRUNC_UDF ( 135.135, -2 )|TRUNC_UDF ( 135.135, 0 )|TRUNC_UDF ( 135.135, 1 )|TRUNC_UDF ( 135.135, 2 )|TRUNC_UDF ( 135.135, 3 )|TRUNC_UDF ( 135.135, 5 )|
--------------------------+--------------------------+-------------------------+-------------------------+-------------------------+----------------------+----------------------+---------------------+----------------------+-----------------------+----------------------+----------------------+---------------------+---------------------+---------------------+--------------------------+--------------------------+--------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+-------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
                         0|                         0|                        1|                      1.0|                     1.00|                     0|                    10|                   15|                  15.7|                  15.79|                     0|                     0|                   -9|                 -9.6|                 -9.6|                         0|                      -900|                      -970|                     -975|                   -975.9|                  -975.97|                 -975.975|                 -975.975|                         0|                      100|                     135|                   135.1|                  135.13|                 135.135|                 135.135|s
Copy

Problèmes connus

Aucun problème n’a été constaté.

EWIs connexes

Pas d’EWIs connexes.

DATEADD UDF INTERVAL

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

Cette UDF est utilisée pour résoudre les opérations avec des intervalles comme :

  • INTERVAL + DATE

  • INTERVAL + TIMESTAMP

  • DATE + INTERVAL

  • DATE + TIMESTAMP

  • INTERVAL + UNKNOWN

  • UNKNOWN + INTERVAL

Note

Un type UNKNOWN est une colonne ou une expression dont le type n’a pas pu être résolu par Snow Convert. Cela arrive souvent lorsque les DDLs des tables ne sont pas incluses dans la migration ou lorsqu’il y a une expression ou une sous-requête qui peut renvoyer différents types de données.

Surcharges UDF personnalisées

DATEADD_UDF(string, date)

Paramètres

  1. INTERVAL_VALUE : L’intervalle String de l’opération.

  2. D : DATE où l’intervalle sera ajouté.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(INTERVAL_VALUE STRING,D DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_UDF(date, string)

Paramètres

  1. D : DATE où l’intervalle sera ajouté.

  2. INTERVAL_VALUE : L’intervalle String de l’opération.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(D DATE, INTERVAL_VALUE STRING)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_UDF(string, timestamp)

Paramètres

  1. INTERVAL_VALUE : L’intervalle String de l’opération.

  2. D : TIMESTAMP où l’intervalle sera ajouté.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_UDF(timestamp, string)

Paramètres

  1. D : TIMESTAMP où l’intervalle sera ajouté.

  2. INTERVAL_VALUE : L’intervalle String de l’opération.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

Exemple d’utilisation

Note

--disableDateAsTimestamp

Indicateur spécifiant si SYSDATE doit être transformé en CURRENT_DATE ou CURRENT_TIMESTAMP. Cela affectera également toutes les colonnes DATE qui seront transformées en TIMESTAMP.

Oracle
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));

CREATE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

INSERT INTO TIMES VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_DATE('06/11/21', 'dd/mm/yy'), 
TO_DATE('05/11/21', 'dd/mm/yy'));

SELECT 
 AsTimeStamp+INTERVAL '1-1' YEAR(2) TO MONTH,
 AsTimeStamp+INTERVAL '2-1' YEAR(4) TO MONTH,
 AsTimeStamp+INTERVAL '1' MONTH,
 AsTimeStamp+INTERVAL '2' MONTH,
 AsDate+INTERVAL '1-1' YEAR(2) TO MONTH,
 AsDate+INTERVAL '2-1' YEAR(4) TO MONTH,
 AsDate+INTERVAL '1' MONTH,
 AsDate+INTERVAL '2' MONTH,
 Unknown+INTERVAL '1 01:00:00.222' DAY TO SECOND(3),
 Unknown+INTERVAL '1 01:10' DAY TO MINUTE,
 Unknown+INTERVAL '1 1' DAY TO HOUR,
 INTERVAL '1' MONTH+AsTimeStamp,
 INTERVAL '1' MONTH+AsDate,
 INTERVAL '1' MONTH+Unknown,
 INTERVAL '2' MONTH+AsTimeStamp,
 INTERVAL '2' MONTH+AsDate,
 INTERVAL '2' MONTH+Unknown
FROM TIMES, UNKNOWN_TABLE;
Copy
|ASTIMESTAMP+INTERVAL'1-1'YEAR(2)TOMONTH|ASTIMESTAMP+INTERVAL'2-1'YEAR(4)TOMONTH|ASTIMESTAMP+INTERVAL'1'MONTH|ASTIMESTAMP+INTERVAL'2'MONTH|ASDATE+INTERVAL'1-1'YEAR(2)TOMONTH|ASDATE+INTERVAL'2-1'YEAR(4)TOMONTH|ASDATE+INTERVAL'1'MONTH|ASDATE+INTERVAL'2'MONTH|UNKNOWN+INTERVAL'101:00:00.222'DAYTOSECOND(3)|UNKNOWN+INTERVAL'101:10'DAYTOMINUTE|UNKNOWN+INTERVAL'11'DAYTOHOUR|INTERVAL'1'MONTH+ASTIMESTAMP|INTERVAL'1'MONTH+ASDATE|INTERVAL'1'MONTH+UNKNOWN|INTERVAL'2'MONTH+ASTIMESTAMP|INTERVAL'2'MONTH+ASDATE|INTERVAL'2'MONTH+UNKNOWN|
|---------------------------------------|---------------------------------------|----------------------------|----------------------------|----------------------------------|----------------------------------|-----------------------|-----------------------|---------------------------------------------|-----------------------------------|-----------------------------|----------------------------|-----------------------|------------------------|----------------------------|-----------------------|------------------------|
|2022-12-05 11:00:00.000                |2023-12-05 11:00:00.000                |2021-12-05 11:00:00.000     |2022-01-05 11:00:00.000     |2022-12-06 00:00:00.000           |2023-12-06 00:00:00.000           |2021-12-06 00:00:00.000|2022-01-06 00:00:00.000|2009-10-02 13:00:00.222                      |2009-10-02 13:10:00.000            |2009-10-02 13:00:00.000      |2021-12-05 11:00:00.000     |2021-12-06 00:00:00.000|2009-11-01 12:00:00.000 |2022-01-05 11:00:00.000     |2022-01-06 00:00:00.000|2009-12-01 12:00:00.000 |

Copy
Snowflake

Note

Cette configuration a été utilisée dans Snowflake

 ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT= 'DD-MON-YY HH.MI.SS.FF6 AM';
ALTER SESSION SET DATE_OUTPUT_FORMAT= 'DD-MON-YY';
Copy
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));
CREATE OR REPLACE TABLE TIMES (
 AsTimeStamp TIMESTAMP(6),
 AsTimestampTwo TIMESTAMP(6),
 AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
 AsDateTwo 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 TIMES
VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "UNKNOWN_TABLE" **

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '1y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '2y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '2 month',
 AsDate+ INTERVAL '1y, 1mm',
 AsDate+ INTERVAL '2y, 1mm',
 AsDate+ INTERVAL '1 month',
 AsDate+ INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1d, 01h, 00m, 00s, 222ms',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1d, 01h, 10m',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1d, 1h',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsDate + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsDate + INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '2 month'
FROM
 TIMES,
 UNKNOWN_TABLE;
Copy
|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''1'' MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''2'' MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''1'' MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''2'' MONTH')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 01:10'' DAY TO MINUTE')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 1'' DAY TO HOUR')|DATEADD_UDF('INTERVAL ''1'' MONTH',ASTIMESTAMP)|DATEADD_UDF('INTERVAL ''1'' MONTH',ASDATE)|DATEADD_UDF('INTERVAL ''1'' MONTH',UNKNOWN)|DATEADD_UDF('INTERVAL ''2'' MONTH',ASTIMESTAMP)|DATEADD_UDF('INTERVAL ''2'' MONTH',ASDATE)|DATEADD_UDF('INTERVAL ''2'' MONTH',UNKNOWN)|
|------------------------------------------------------------|------------------------------------------------------------|-----------------------------------------------|-----------------------------------------------|-------------------------------------------------------|-------------------------------------------------------|------------------------------------------|------------------------------------------|-------------------------------------------------------------------|---------------------------------------------------------|---------------------------------------------------|-----------------------------------------------|------------------------------------------|-------------------------------------------|-----------------------------------------------|------------------------------------------|-------------------------------------------|
|2022-12-05 11:00:00.000                                     |2023-12-05 11:00:00.000                                     |2021-12-05 11:00:00.000                        |2022-01-05 11:00:00.000                        |2022-12-06                                             |2023-12-06                                             |2021-12-06                                |2022-01-06                                |2009-10-02 13:00:00.222                                            |2009-10-02 13:10:00.000                                  |2009-10-02 13:00:00.000                            |2021-12-05 11:00:00.000                        |2021-12-06                                |2009-11-01 12:00:00.000                    |2022-01-05 11:00:00.000                        |2022-01-06                                |2009-12-01 12:00:00.000                    |

Copy

Problèmes connus

1. INTERVAL + INTERVAL Operation is not supported

Snowflake ne prend pas en charge les opérations INTERVAL + INTERVAL.

EWIs connexes

  1. SSC-EWI-OR0036 : Problèmes de résolution de types, l’opération arithmétique peut ne pas se comporter correctement entre une chaîne et une date.

  2. SSC-EWI-OR0095 : L’opération entre le type d’intervalle et le type de date n’est pas prise en charge.

  3. SSC-FDM-0007 : Élément avec des dépendances manquantes.

  4. SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent.

DATEDIFF UDF INTERVAL

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

Cette UDF est utilisée pour résoudre les opérations avec des intervalles comme :

  • INTERVAL - UNKNOWN

  • UNKNOWN - INTERVAL

  • DATE - INTERVAL

  • TIMESTAMP - INTERVAL

Note

Un type UNKNOWN est une colonne ou une expression dont le type n’a pas pu être résolu par Snow Convert. Cela arrive souvent lorsque les DDLs des tables ne sont pas incluses dans la migration ou lorsqu’il y a une expression ou une sous-requête qui peut renvoyer différents types de données.

Surcharges UDF personnalisées

DATEADD_DDIF(string, date)

Paramètres

  1. INTERVAL_VALUE : L’intervalle String de l’opération.

  2. D : DATE où l’intervalle sera soustrait.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_DIFF(date, string)

Paramètres

  1. D : DATE où l’intervalle sera soustrait.

  2. INTERVAL_VALUE : L’intervalle String de l’opération.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D DATE, INTERVAL_VALUE STRING)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_DIFF(string, timestamp)

Paramètres

  1. INTERVAL_VALUE : L’intervalle String de l’opération.

  2. D : TIMESTAMP où l’intervalle sera soustrait.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_DIFF(timestamp, string)

Paramètres

  1. D : TIMESTAMP où l’intervalle sera soustrait.

  2. INTERVAL_VALUE : L’intervalle String de l’opération.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

Exemple d’utilisation

Note

--disableDateAsTimestamp

Indicateur spécifiant si SYSDATE doit être transformé en CURRENT_DATE ou CURRENT_TIMESTAMP. Cela affectera également toutes les colonnes DATE qui seront transformées en TIMESTAMP.

Oracle
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));

CREATE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

INSERT INTO TIMES VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_DATE('06/11/21', 'dd/mm/yy'), 
TO_DATE('05/11/21', 'dd/mm/yy'));

SELECT 
 AsTimeStamp-INTERVAL '1-1' YEAR(2) TO MONTH,
 AsTimeStamp-INTERVAL '2-1' YEAR(4) TO MONTH,
 AsTimeStamp-INTERVAL '1' MONTH,
 AsTimeStamp-INTERVAL '2' MONTH,
 AsDate-INTERVAL '1-1' YEAR(2) TO MONTH,
 AsDate-INTERVAL '2-1' YEAR(4) TO MONTH,
 AsDate-INTERVAL '1' MONTH,
 AsDate-INTERVAL '2' MONTH,
 Unknown-INTERVAL '1 01:00:00.222' DAY TO SECOND(3),
 Unknown-INTERVAL '1 01:10' DAY TO MINUTE,
 Unknown-INTERVAL '1 1' DAY TO HOUR
FROM TIMES, UNKNOWN_TABLE;
Copy
|ASTIMESTAMP-INTERVAL'1-1'YEAR(2)TOMONTH|ASTIMESTAMP-INTERVAL'2-1'YEAR(4)TOMONTH|ASTIMESTAMP-INTERVAL'1'MONTH|ASTIMESTAMP-INTERVAL'2'MONTH|ASDATE-INTERVAL'1-1'YEAR(2)TOMONTH|ASDATE-INTERVAL'2-1'YEAR(4)TOMONTH|ASDATE-INTERVAL'1'MONTH|ASDATE-INTERVAL'2'MONTH|UNKNOWN-INTERVAL'101:00:00.222'DAYTOSECOND(3)|UNKNOWN-INTERVAL'101:10'DAYTOMINUTE|UNKNOWN-INTERVAL'11'DAYTOHOUR|
|---------------------------------------|---------------------------------------|----------------------------|----------------------------|----------------------------------|----------------------------------|-----------------------|-----------------------|---------------------------------------------|-----------------------------------|-----------------------------|
|2020-10-05 11:00:00.000                |2019-10-05 11:00:00.000                |2021-10-05 11:00:00.000     |2021-09-05 11:00:00.000     |2020-10-06 00:00:00.000           |2019-10-06 00:00:00.000           |2021-10-06 00:00:00.000|2021-09-06 00:00:00.000|2009-09-30 10:59:59.778                      |2009-09-30 10:50:00.000            |2009-09-30 11:00:00.000      |

Copy
Snowflake

Note

Cette configuration a été utilisée dans Snowflake

 ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT= 'DD-MON-YY HH.MI.SS.FF6 AM';
ALTER SESSION SET DATE_OUTPUT_FORMAT= 'DD-MON-YY';
Copy
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));
CREATE OR REPLACE TABLE TIMES (
 AsTimeStamp TIMESTAMP(6),
 AsTimestampTwo TIMESTAMP(6),
 AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
 AsDateTwo 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 TIMES
VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "UNKNOWN_TABLE" **

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '1y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '2y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '2 month',
 AsDate- INTERVAL '1y, 1mm',
 AsDate- INTERVAL '2y, 1mm',
 AsDate- INTERVAL '1 month',
 AsDate- INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 01h, 00m, 00s, 222ms',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 01h, 10m',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 1h'
FROM
 TIMES,
 UNKNOWN_TABLE;
Copy
|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''1'' MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''2'' MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''1'' MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''2'' MONTH')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 01:10'' DAY TO MINUTE')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 1'' DAY TO HOUR')|
|-------------------------------------------------------------|-------------------------------------------------------------|------------------------------------------------|------------------------------------------------|--------------------------------------------------------|--------------------------------------------------------|-------------------------------------------|-------------------------------------------|--------------------------------------------------------------------|----------------------------------------------------------|----------------------------------------------------|
|2020-10-05 11:00:00.000                                      |2019-10-05 11:00:00.000                                      |2021-10-05 11:00:00.000                         |2021-09-05 11:00:00.000                         |2020-10-06                                              |2019-10-06                                              |2021-10-06                                 |2021-09-06                                 |2009-09-30 10:59:59.778                                             |2009-09-30 10:50:00.000                                   |2009-09-30 11:00:00.000                             |
Copy

Problèmes connus

1. INTERVAL - INTERVAL Operation is not supported

Snowflake ne prend pas en charge les opérations INTERVAL - INTERVAL.

EWIs connexes

  1. SSC-EWI-OR0036 : Problèmes de résolution de types, l’opération arithmétique peut ne pas se comporter correctement entre une chaîne et une date.

  2. SSC-EWI-OR0095 : L’opération entre le type d’intervalle et le type de date n’est pas prise en charge.

  3. SSC-FDM-0007 : Élément avec des dépendances manquantes.

  4. SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent.