SnowConvert AI - Function References for SQL-Server¶

Definition¶

This user-defined function (UDF) determines whether an expression is a valid numeric type.

ISNUMERIC_UDF(EXPR VARCHAR)

Parameters¶

EXPR VARCHAR

The expression to be evaluated.

Returns¶

Returns 1 when the input expression evaluates to a valid numeric data type; otherwise, it returns 0.

Usage example¶

Input:

SELECT ISNUMERIC_UDF('5');

Output:

1

Definition¶

This user-defined function (UDF) returns the starting position of the first occurrence of a pattern in a specified expression or zeros if the pattern is not found.

PATINDEX_UDF(PATTERN VARCHAR, EXPRESSION VARCHAR)

Parameters¶

PATTERN VARCHAR

The pattern to search for.

EXPRESSION VARCHAR

The expression that is being evaluated.

Returns¶

Returns an integer with the starting position of the pattern.

Usage example¶

Input:

SELECT PATINDEX_UDF('an', 'banana');

Output:

2

Definition¶

This user-defined function (UDF) gets a value indicating the severity of an error. The default value will always be 16.

ERROR_SEVERITY_UDF()

Parameters¶

No input parameters.

Returns¶

Returns a string with the value associated with the SQL variable name ERROR_SEVERITY.

Usage example¶

Input:

SELECT ERROR_SEVERITY_UDF();

Output:

null -- No information set.

Definition¶

This user-defined function (UDF) emulates the behavior of embedded parameters (Data Binding) in the SP_EXECUTESQL system procedure by directly replacing their values in the SQL string.

Additionally, it removes the OUTPUT parameters from the string as this is done outside the EXECUTE IMMEDIATE to which the SP_EXECUTESQL will be transformed.

For more information, check the SP_EXECUTESQL translation specification.

TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(
    _SQL_STRING STRING,
    _PARAMS_DEFINITION STRING,
    _PARAMS_NAMES ARRAY,
    _PARAMS_VALUES ARRAY
)

Parameters¶

_SQL_STRING STRING

The string to be transformed.

_PARAMS_DEFINITION STRING

The original parameters definition checks the order in which parameter values must be assigned.

_PARAMS_NAMES ARRAY

The array of parameter names to replace the values in the SQL string.

_PARAMS_VALUES ARRAY

The array of the parameter values to be replaced in the SQL string.

Returns¶

Returns a STRING with the embedded parameters values replaced.

Usage example¶

Input:

SELECT TRANSFORM_SP_EXECUTE_SQL_STRING_UDF(
    'SELECT * FROM PERSONS WHERE NAME LIKE (@NAME) AND ID < @id AND AGE < @age;', '@age INT, @id INT, @name VARCHAR(25)',
    ARRAY_CONSTRUCT('', '', ''),
    ARRAY_CONSTRUCT(30, 100, 'John Smith'));

Output:

SELECT * FROM PERSONS WHERE NAME LIKE ('John Smith') AND ID < 100 AND AGE < 30;

Definition¶

This user-defined function (UDF) checks if a table with a specific name has been created before.

TABLE_OBJECT_ID_UDF(NAME VARCHAR)

Parameters¶

NAME VARCHAR

The table name to be evaluated.

Returns¶

Returns a boolean expression depending on the existence of the table.

Usage example¶

Input:

SELECT TABLE_OBJECT_ID_UDF('Test');

Output:

FALSE

Definition¶

This user-defined function (UDF) returns the value associated with the SQL variable name ERROR_PROCEDURE.

ERROR_PROCEDURE_UDF()

Parameters¶

No input parameters.

Returns¶

Returns a string with the value associated with the SQL variable name ERROR_PROCEDURE.

Usage example¶

Input:

SELECT ERROR_PROCEDURE_UDF();

Output:

null -- No information set.

Definition¶

This user-defined function (UDF) emulates the DB_ID (https://learn.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql?view=sql-server-ver16) functionality.

DB_ID_UDF(p_database_name STRING)

Parameters¶

p_database_name STRING

The name of the database to obtain the id.

Returns¶

Returns an id which corresponds to the number assigned to the database when it is created. This number is assigned consecutively.

Usage example¶

Input:

SELECT DB_ID_UDF('MY_DATABASE')

Output:

6

Definition¶

This user-defined function (UDF) returns the value associated with the SQL variable name ERROR_LINE.

ERROR_LINE_UDF()

Parameters¶

No input parameters.

Returns¶

Returns a string with the value associated with the SQL variable name ERROR_LINE.

Usage example¶

Input:

SELECT ERROR_LINE_UDF();

Output:

null -- No information set.

Definition¶

This user-defined function (UDF) checks if a function with a specific name has been created before.

VIEW_OBJECT_ID_UDF(NAME VARCHAR)

Parameters¶

NAME VARCHAR

The function name to be evaluated.

Returns¶

Returns a boolean expression depending on the existence of the function.

Usage example¶

Input:

SELECT FUNCTION_OBJECT_ID_UDF('Test');

Output:

FALSE

Definition¶

This user-defined function (UDF) checks if a constraint with a specific name has been created before.

CONSTRAINT_OBJECT_ID_UDF(NAME VARCHAR)

Parameters¶

NAME VARCHAR

The constraint name to be evaluated.

Returns¶

Returns a boolean expression depending on the existence of the constraint.

Usage example¶

Input:

SELECT CONSTRAINT_OBJECT_ID_UDF('Test');

Output:

FALSE

Definition¶

This user-defined function (UDF) converts an object to XML.

FOR_XML_UDF(OBJ OBJECT, ELEMENT_NAME VARCHAR, ROOT_NAME VARCHAR)

Parameters¶

OBJ OBJECT

Object to be converted.

ELEMENT_NAME VARCHAR

Element name to be given the object.

ROOT_NAME VARCHAR

The root name for XML.

Returns¶

Returns a varchar in the format of XML.

Usage example¶

Input:

SELECT
FOR_XML_UDF(OBJECT_CONSTRUCT('id', 1, 'name', 'David'), 'employee', 'employees');

Output:

<employees>
    <employee type="OBJECT">
        <id type="INTEGER">1</id>
        <name type="VARCHAR">David</name>
    </employee>
<employees>

Definition¶

This user-defined function (UDF) checks if an object with a specific name has been created before.

OBJECT_ID_UDF(NAME VARCHAR)

Parameters¶

NAME VARCHAR

The object name to be evaluated.

Returns¶

Returns a boolean expression depending on the existence of the object.

Usage example¶

Input:

SELECT OBJECT_ID_UDF('Test');

Output:

FALSE

Definition¶

This user-defined function (UDF) checks if a procedure with a specific name has been created before.

PROCEDURE_OBJECT_ID_UDF(NAME VARCHAR)

Parameters¶

NAME VARCHAR

The procedure name to be evaluated.

Returns¶

Returns a boolean expression depending on the existence of the procedure.

Usage example¶

Input:

SELECT PROCEDURE_OBJECT_ID_UDF('Test');

Output:

FALSE

Definition¶

This user-defined function (UDF) determines whether the input value is a valid date.

ISDATE_UDF(DATE_VALUE STRING)

Parameters¶

DATE_VALUE STRING

The date that is going to be evaluated.

Returns¶

Returns 1 when the input expression evaluates to a valid date data type; otherwise, it returns 0.

Usage example¶

Input:

SELECT ISDATE_UDF('2024-01-26');

Output:

1

Definition¶

This user-defined function (UDF) returns the value associated with the SQL variable name ERROR_NUMBER.

ERROR_NUMBER_UDF()

Parameters¶

No input parameters.

Returns¶

Returns a string with the value associated with the SQL variable name ERROR_NUMBER.

Usage example¶

Input:

SELECT ERROR_NUMBER_UDF();

Output:

null -- No information set.

Definition¶

This user-defined function (UDF) is an auxiliary function to format the offset hour and its prefix operator.

OFFSET_FORMATTER(offset_hrs VARCHAR)

Parameters¶

offset_hrs VARCHAR

The value to be formatted.

Returns¶

Returns a varchar value with the formatted output for the offset.

Usage example¶

Input:

 SELECT OFFSET_FORMATTER('2024-01-26 22:00:50.708 -0800');

Output:

2024-01-26 22:00:50.708 -0800

Definition¶

This user-defined function (UDF) generates a query from an XML reading.

OPENXML_UDF(XML VARCHAR, PATH VARCHAR)

Parameters¶

XML VARCHAR

The XML content as a varchar.

PATH VARCHAR

The path of the node to extract.

Returns¶

Returns a table with the data generated by the XML reading.

Usage example¶

Input:

SELECT * FROM TABLE(OPENXML_UDF('<iceCreamOrders>
    <order>
        <customer customerID="CUST001" contactName="Test ABC">
            <iceCreamOrder orderID="ORD001" employeeID="101" orderDate="2023-05-15T14:30:00">
                <iceCreamDetail productID="001" quantity="2"/>
                <iceCreamDetail productID="003" quantity="1"/>
            </iceCreamOrder>
        </customer>
    </order>
    <order>
        <customer customerID="CUST002" contactName="Test XYZ">
            <iceCreamOrder orderID="ORD002" employeeID="102" orderDate="2023-06-20T12:45:00">
                <iceCreamDetail productID="005" quantity="3"/>
                <iceCreamDetail productID="007" quantity="2"/>
            </iceCreamOrder>
        </customer>
    </order>
</iceCreamOrders>
', 'iceCreamOrders:order'));

Output:

Definition¶

This user-defined function (UDF) creates a valid SQL Server delimited identifier by returning a Unicode string with the delimiters added.

QUOTENAME_UDF(STR VARCHAR, QUOTECHAR VARCHAR)

Parameters¶

STR VARCHAR

The string to be transformed.

QUOTECHAR VARCHAR

The delimiter to add to the first parameter.

Returns¶

Returns a varchar with the second parameter identifier added as delimiter.

Usage example¶

Input:

SELECT QUOTENAME_UDF('test', '?');

Output:

?test?

Definition¶

This user-defined function (UDF) updates the error variables in an environment in order to know when the procedure throws an error.

UPDATE_ERROR_VARS_UDF(MESSAGE STRING, SEVERITY STRING, STATE STRING)

Parameters¶

STATE STRING

The state of the error message.

MESSAGE STRING

The message to be shown in the error.

SEVERITY STRING

The severity of the error.

Returns¶

Returns a string value with the new error message information.

Usage example¶

Input:

  SELECT UPDATE_ERROR_VARS_UDF('Message', '1', '1');

Output:

1ABC1

Definition¶

This user-defined function (UDF) is a function that rounds milliseconds to increments of 0, 3, or 7 milliseconds. Transact automatically rounds the milliseconds of datetime values.

ROUND_MILLISECONDS_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The input time to be rounded.

Returns¶

Returns the same input TIMESTAMP_TZ value but with the milliseconds rounded.

Usage example¶

Input:

SELECT PUBLIC.ROUND_MILLISECONDS_UDF('1900-01-01 00:00:00.995 +0100')

Output:

'1900-01-01 00:00:00.997 +0100'

Definition¶

This user-defined function (UDF) is used to cast a numeric value to timestamp_tz.

CAST_NUMERIC_TO_TIMESTAMP_TZ_UDF(INPUT NUMBER)

Parameters¶

INPUT NUMBER

The number to be cast.

Returns¶

Returns a timestamp_tz with the current timezone.

Usage example¶

Input:

SELECT PUBLIC.CAST_NUMERIC_TO_TIMESTAMP_TZ_UDF(0)

Output:

1900-01-01 01:00:00.000 +0100

Definition¶

This user-defined function (UDF) determines whether an expression is a valid numeric type.

IDENTITY_UDF()

Parameters¶

No input parameters.

Returns¶

Returns an integer expression.

Usage example¶

Input:

IDENTITY_UDF()

Output:

1

Definition¶

This user-defined function (UDF) converts an object to XML.

FOR_XML_UDF(OBJ OBJECT, ELEMENT_NAME VARCHAR)

Parameters¶

OBJ OBJECT

Object to be converted.

ELEMENT_NAME VARCHAR

Element name to be given the object.

Returns¶

Returns a varchar in the format of XML.

Usage example¶

Input:

SELECT
FOR_XML_UDF(OBJECT_CONSTRUCT('id', 1, 'name', 'David'), 'employee');

Output:

<employee type="OBJECT">
    <id type="INTEGER">1</id>
    <name type="VARCHAR">David</name>
</employee>

Definition¶

This user-defined function (UDF) creates a valid SQL Server delimited identifier by returning a Unicode string with the delimiters added.

QUOTENAME_UDF(STR VARCHAR)

Parameters¶

STR VARCHAR

The string to be transformed.

Returns¶

Returns a varchar with the delimited identifier added.

Usage example¶

Input:

SELECT QUOTENAME_UDF('test');

Output:

"test"

Definition¶

This user-defined function (UDF) checks if a view with a specific name has been created before.

VIEW_OBJECT_ID_UDF(NAME VARCHAR)

Parameters¶

NAME VARCHAR

The view name to be evaluated.

Returns¶

Returns a boolean expression depending on the existence of the view.

Usage example¶

Input:

SELECT VIEW_OBJECT_ID_UDF('Test');

Output:

FALSE

Definition¶

This user-defined function (UDF) converts both inputs to the system session timezone and subtracts the dates (FIRST_DATE - SECOND_DATE) taking 1900-01-01 00:00:00.000 as the zero value. If any value does not include the timezone, the current session timezone is used.

PUBLIC.SUBTRACT_TIMESTAMP_TZ_UDF(FIRST_DATE TIMESTAMP_TZ, SECOND_DATE TIMESTAMP_TZ)

Parameters¶

FIRST_DATE TIMESTAMP_TZ

The first date to be subtracted from.

SECOND_DATE TIMESTAMP_TZ

The second date to be subtracted to.

Returns¶

Returns the difference between the two input dates.

Usage example¶

Input:

SELECT SUBTRACT_TIMESTAMP_TZ_UDF('1900-01-01 00:00:00.000 +0100', '1900-01-01 00:00:00.003 -0100')

Output:

1899-12-31 13:59:59.997 -0800

Definition¶

This user-defined function (UDF) is a template for translating the functionality of SQL Server STR() to Snowflake when it’s used with one or two optional parameters

STR_UDF(FLOAT_EXPR FLOAT, FORMAT VARCHAR)

Parameters¶

FLOAT_EXPR FLOAT

The expression to be processed.

FORMAT VARCHAR

The format to apply.

Returns¶

Returns a varchar with the formatted expression.

Usage example¶

Input:

SELECT STR_UDF(1.5, '99');

Output:

2

Definition¶

This user-defined function (UDF) generates an object with the information from executing a reading from an XML value.

XML_JSON_SIMPLE(XML VARIANT)

Parameters¶

XML VARIANT

The XML to be read.

Returns¶

Returns an object with the processed information from the XML.

Usage example¶

Input:

SELECT XML_JSON_SIMPLE(TO_VARIANT(PARSE_XML('<iceCreamOrders>
    <order>
        <customer customerID="CUST001" contactName="Test ABC">
            <iceCreamOrder orderID="ORD001" employeeID="101" orderDate="2023-05-15T14:30:00">
                <iceCreamDetail productID="001" quantity="2"/>
                <iceCreamDetail productID="003" quantity="1"/>
            </iceCreamOrder>
        </customer>
    </order>
    <order>
        <customer customerID="CUST002" contactName="Test XYZ">
            <iceCreamOrder orderID="ORD002" employeeID="102" orderDate="2023-06-20T12:45:00">
                <iceCreamDetail productID="005" quantity="3"/>
                <iceCreamDetail productID="007" quantity="2"/>
            </iceCreamOrder>
        </customer>
    </order>
</iceCreamOrders>
')));

Output:

{
  "iceCreamOrders": {
    "$name": "iceCreamOrders",
    "order": [
      {
        "order": {
          "$name": "order",
          "customer": [
            {
              "customer": {
                "$name": "customer",
                "@contactName": "Test ABC",
                "@customerID": "CUST001",
                "iceCreamOrder": [
                  {
                    "iceCreamOrder": {
                      "$name": "iceCreamOrder",
                      "@employeeID": 101,
                      "@orderDate": "2023-05-15T14:30:00",
                      "@orderID": "ORD001",
                      "iceCreamDetail": [
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "001",
                            "@quantity": 2
                          }
                        },
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "003",
                            "@quantity": 1
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "order": {
          "$name": "order",
          "customer": [
            {
              "customer": {
                "$name": "customer",
                "@contactName": "Test XYZ",
                "@customerID": "CUST002",
                "iceCreamOrder": [
                  {
                    "iceCreamOrder": {
                      "$name": "iceCreamOrder",
                      "@employeeID": 102,
                      "@orderDate": "2023-06-20T12:45:00",
                      "@orderID": "ORD002",
                      "iceCreamDetail": [
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "005",
                            "@quantity": 3
                          }
                        },
                        {
                          "iceCreamDetail": {
                            "$name": "iceCreamDetail",
                            "@productID": "007",
                            "@quantity": 2
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}

Definition¶

This user-defined function (UDF) provides the functionality of the SQL Server FORMATMESSAGE function. It constructs a message from an existing message from a provided string.

FORMATMESSAGE_UDF(MESSAGE STRING, ARGS ARRAY)

Parameters¶

MESSAGE STRING

The existing message string.

ARGS ARRAY

The arguments to be added on the first message string.

Returns¶

Returns a string with the corresponding concatenated message related to the argument’s positions.

Usage example¶

Input:

SELECT FORMATMESSAGE_UDF('Test %s!', TO_ARRAY('a'));

Output:

Test a!

Definition¶

This user-defined function (UDF) determines the windows group membership by examining an access token.

IS_MEMBER_UDF(ROLE STRING)

Parameters¶

ROLE STRING

The role name to be checked.

Returns¶

Returns a boolean expression on true when the current user is a member of the role; otherwise returns false.

Usage example¶

Input:

SELECT IS_MEMBER_UDF('TEST');

Output:

FALSE

Definition¶

This user-defined function (UDF) throws an exception with a specific message.

RAISERROR_UDF(MSG_ID DOUBLE, SEVERITY DOUBLE, STATE DOUBLE, PARAMS ARRAY)

Parameters¶

MSG_ID DOUBLE

The message ID of the error message.

SEVERITY DOUBLE

The severity number for the error.

STATE DOUBLE

The state number for the error message.

PARAMS ARRAY

The additional information of the error message.

Returns¶

Returns a varchar with an error message.

Usage example¶

Input:

SELECT RAISERROR_UDF(2.1, 1.6, 1.0, array_construct('More information'));

Output:

MESSAGE: 2.1, LEVEL: 1.6, STATE: 1

Definition¶

This user-defined function (UDF) is a template for translating the functionality of SQL Server STR() to Snowflake when it’s used with one or two optional parameters

STR_UDF(FLOAT_EXPR FLOAT, FORMAT VARCHAR)

Parameters¶

FLOAT_EXPR FLOAT

The expression to be processed.

Returns¶

Returns a varchar with the formatted expression.

Usage example¶

Input:

SELECT STR_UDF(1.5);

Output:

2

Definition¶

This user-defined function (UDF) returns a new timestamp_tz with the adjusted time taken for parameter target_tz.

SWITCHOFFSET_UDF(source_timestamp TIMESTAMP_TZ, target_tz varchar)

Parameters¶

source_timestamp TIMESTAMP_TZ

The source timestamp to adjust.

target_tz varchar

The target time to take.

Returns¶

Returns the formatted target time as TIMESTAMP_TZ.

Usage example¶

Input:

SELECT SWITCHOFFSET_UDF(time_in_paris, '-0600') as time_in_costa_rica;

Output:

Definition¶

This user-defined function (UDF) gets the current session or system timezone as a literal.

GET_CURRENT_TIMEZONE_UDF()

Parameters¶

No parameters.

Returns¶

Returns a literal value with the current session or system timezone as a literal.

Usage example¶

Input:

SELECT PUBLIC.GET_CURRENT_TIMEZONE_UDF();

Output:

'Europe/London'

Definition¶

This user-defined function (UDF) updates the error variables in an environment in order to know when the procedure throws an error.

UPDATE_ERROR_VARS_UDF(LINE STRING,CODE STRING, STATE STRING, MESSAGE STRING, PROC_NAME STRING, SEVERITY STRING)

Parameters¶

LINE STRING

The line related to the error.

CODE STRING

The error code associated with the error message.

STATE STRING

The state of the error message.

MESSAGE STRING

The message to be shown in the error.

PROC_NAME STRING

The procedure name.

SEVERITY STRING

The severity of the error.

Returns¶

Returns a string value with the new error message information.

Usage example¶

Input:

  SELECT UPDATE_ERROR_VARS_UDF('1', '1', '1', 'ABC', 'TEST', '1');

Output:

111ABCTEST1

Definition¶

This user-defined function (UDF) checks if a sequence with a specific name has been created before.

SEQUENCE_OBJECT_ID_UDF(NAME VARCHAR)

Parameters¶

NAME VARCHAR

The sequence name to be evaluated.

Returns¶

Returns a boolean expression depending on the existence of the sequence.

Usage example¶

Input:

SELECT SEQUENCE_OBJECT_ID_UDF('Test');

Output:

FALSE

Definition¶

This user-defined function (UDF) is used to cast timestamp_tz to numeric. It converts the current timezone to UTC because the numeric value cannot save the timestamp information.

CAST_TIMESTAMP_TZ_TO_NUMERIC_UDF(INPUT TIMESTAMP_TZ)

Parameters¶

INPUT TIMESTAMP_TZ

The timestamp input that is going to be cast.

Returns¶

Returns a numeric with a decimal point. The integer part represents the number of days from 1900-01-01 and the decimal part is the percentage of milliseconds in 24 hours.

Usage example¶

Input:

SELECT PUBLIC.CAST_TIMESTAMP_TZ_TO_NUMERIC_UDF('1900-01-01 01:00:00.000 +0100')

Output:

0

Definition¶

This user-defined function (UDF) throws an exception with a specific message.

RAISERROR_UDF(MSG_TEXT VARCHAR, SEVERITY DOUBLE, STATE DOUBLE, PARAMS ARRAY)

Parameters¶

MSG_TEXT VARCHAR

The message text of the error message.

SEVERITY DOUBLE

The severity number for the error.

STATE DOUBLE

The state number for the error message.

PARAMS ARRAY

The additional information of the error message.

Returns¶

Returns a varchar with an error message.

Usage example¶

Input:

SELECT RAISERROR_UDF('<\<%*.*s>> TEST', 1.0, 1, array_construct());

Output:

MESSAGE: <<undefined>> TEST, LEVEL: 1, STATE: 1

Definition¶

This user-defined function (UDF) gets the PART_NUMBER index of a string separated by '.'.

PARSENAME_UDF(STR VARCHAR, PART_NUMBER INT)

Parameters¶

STR VARCHAR

The object name as a string.

PART_NUMBER INT

The part of the object name to be checked.

Returns¶

Returns the specified part of an object name.

Usage example¶

Input:

SELECT PARSENAME_UDF('Test_A.Test_B.Test_C]', 2);

Output:

Test_B

Definition¶

This user-defined function (UDF) gets the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block.

ERROR_STATE_UDF()

Parameters¶

No input parameters.

Returns¶

Returns the string with the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block.

Usage example¶

Input:

SELECT ERROR_STATE_UDF();

Output:

null -- No information set.

Definition¶

This user-defined function (UDF) casts time to timestamp_tz.

CAST_TIME_TO_TIMESTAMP_TZ_UDF(INPUT TIME)

Parameters¶

INPUT TIME

The input time to be cast to timestamp_tz.

Returns¶

Returns a timestamp_tz with the date as 1900-01-01 and the same time as the input.

Usage example¶

Input:

SELECT PUBLIC.CAST_TIME_TO_TIMESTAMP_TZ_UDF('00:00:00.995')

Output:

1900-01-01 00:00:00.997

Definition¶

This user-defined function (UDF) converts both inputs to the system or session timezone and sums the dates taking 1900-01-01 00:00:00.000 as the zero value. If any value does not include the timezone, the current session timezone is used.

SUM_TIMESTAMP_TZ_UDF(FIRST_DATE TIMESTAMP_TZ, SECOND_DATE TIMESTAMP_TZ)

Parameters¶

FIRST_DATE TIMESTAMP_TZ

The first date to sum to.

SECOND_DATE TIMESTAMP_TZ

The second date to sum to.

Returns¶

Returns the sum between the two input dates.

Usage example¶

Input:

SELECT SUM_TIMESTAMP_TZ_UDF('1900-01-01 00:00:00.000 +0100', '1900-01-01 00:00:00.003 -0100')

Output:

1900-01-01 00:00:00.003 +0000

Definition¶

This user-defined function (UDF) retrieves the WEEK_START configuration, which is equivalent to the @@FIRSTDATE function. To maintain consistency across platforms, ensure the WEEK_START parameter matches the DATEFIRST setting in Transact-SQL.

GET_WEEK_START_UDF()

Returns¶

Returns a number representing the first day of the week.

Usage example¶

Snowflake’s default value for WEEK_START is 0. However, this function returns 7 to align with the default DATEFIRST value in Transact-SQL, ensuring consistent behavior.

Input:

SELECT GET_WEEK_START_UDF();

Output:

7

Definition¶

This user-defined function (UDF) gets the day of the week as a number (1-7) To ensure the consistency across platforms, please set the WEEK_START parameter to the same value as the DATEFIRST setting in Transact-SQL.

DATE_PART_WEEK_DAY_UDF(INPUT DATE)

Parameters¶

INPUT DATE

Date to get the day.

Returns¶

Returns a number representing the day of the week where Monday=1, Tuesday=2, …, Sunday=7.

Usage example¶

The WEEK_START parameter is 0, which causes the DATE_PART_WEEK_DAY_UDF to return a value of 1.

Input:

SELECT PUBLIC.DATE_PART_WEEK_DAY_UDF('2025-08-17') AS "Sunday";

Output:

1