How to call mysql function in snowflake UDF

Question

I am trying to write below UDF in snowflake.But it doesnt allow to use sql functions like TO_DATE,DATE_ADD,dayofweek . Any alternative idea would be helpful. Thanks.

CREATE OR REPLACE FUNCTION getShippingDate(deliveryDate varchar,deliveryCountryCode varchar, holidayList varchar,deliveryDays varchar) 
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS $$

    deliveryPeriod = 0
    weekDay = 0
    deliveryDate= TO_DATE(deliveryDate)

    if(deliveryCountryCode != 'IN') {
        deliveryPeriod = 2
    }
    else {
        deliveryPeriod = deliveryDays + 1
    }   

    if(deliveryPeriod <= 0) {
        deliveryPeriod = 1
    }

    /* substract delivery period from delivery date */
    deliveryDate = DATEADD(Day ,-deliveryPeriod, to_date(deliveryDate))  

    weekDay = dayofweek(deliveryDate) 

    /* if shipping date falls on sunday then substract 2 days */
    if (weekDay == 0) {
         deliveryDate =  DATEADD(Day ,-2, to_date(deliveryDate)) 
     }    
    /* if shipping date falls on saturday then substract 1 days */ 
    if(weekDay == 6){
        deliveryDate = DATEADD(Day ,-1, to_date(deliveryDate)) 
    }

    /* check if shipping date falls on holiday then substract 1 */
    if(charindex(deliveryDate , holidayList) > 0) {
        deliveryDate = DATEADD(Day ,-1, to_date(deliveryDate)) 
    }
    return deliveryDate
$$```

score 2 · Accepted Answer · answered Dec 17 '19 at 16:41

2

It is relatively easy to create date functions in JavaScript. Look at example below.
Just remember:

Please use the DATE SQL datatype for input and output
Parameters must be "Quoted" if you want to reference them in MixedCase
Use a semicolon ; to terminate each statement

CREATE OR REPLACE FUNCTION getShippingDate("deliveryDate" DATE, "offset" FLOAT) 
RETURNS DATE
LANGUAGE JAVASCRIPT
AS $$
  function day_add(dt, days) {
    return new Date(dt.getFullYear(), dt.getMonth(), dt.getDate() + offset);
  }
  return day_add(deliveryDate, offset);
$$;
SELECT getShippingDate(CURRENT_DATE, -2);

new Date() does some magic when you add or subtract outside the days of the month.

answered Dec 17 '19 at 16:41

Hans Henrik Eriksen

2,690
5
12

By the way, SQL `DATE_FROM_PARTS()` does the same magic as `new Date`, eg, `DATE_FROM_PARTS(year, 1, 0)` gets you New Year's Eve from the year before. – Hans Henrik Eriksen Dec 18 '19 at 06:52
Hi Hans, Amazing thank you. I saw that but i wasn't able to use that in snowflake, i haven't seen that kind of example in snowflake documentation. Thank you for your idea and I got something new to learn. – Krishna Dec 18 '19 at 07:24
You're welcome, examples in the docs are somewhat on the thin side. I've ended up experimenting with parameters for about everything, and it is time consuming. But sometimes rewarding. – Hans Henrik Eriksen Dec 18 '19 at 07:44
1

My example with `DATE_FROM_PARTS()` was also on the thin side. The function has similar properties as `DATEADD()`. Month and day parameters can be any number. Eg. in `DATE_FROM_PARTS(2019, 13, 32)` both month and day overflows, and you end up with `2020-02-01`. – Hans Henrik Eriksen Dec 18 '19 at 07:54

waldente · Answer 2 · 2019-12-17T14:07:57.420

0

Implementing your function as a scalar SQL UDF would solve the problem in this case. Or you could implement your own javascript date math. For example. The good news is that you can call JS UDFs from your SQL UDFs.

edited Dec 17 '19 at 14:07

answered Dec 17 '19 at 13:53

waldente

1,324
9
12

score -1 · Answer 3 · answered Dec 17 '19 at 11:21

-1

You may need to try like below:

CREATE OR REPLACE PROCEDURE dbo.usp_test() returns VARCHAR LANGUAGE javascript EXECUTE AS CALLER AS
$$
snowflake.createStatement( {sqlText: "SET dt = DATEADD(MONTH, 1,CURRENT_TIMESTAMP)"} ).execute(); return 'success';
$$ ;

-- CALL ds_work.ddd.usp_test()

answered Dec 17 '19 at 11:21

Vasu G

34
3

Thanks for your effort but i want the same in UDF so that i can use the same in my select query. It is of course possible in stored procedures. – Krishna Dec 17 '19 at 12:12

How to call mysql function in snowflake UDF

3 Answers3