CODEWITHSUNDEEP
functionsnowflake-cloud-data-platform
Sue
Sue

Reputation: 3

Functions - Snowflake

-I'm trying to create a function that returns the date and the last time of that date, eg. 2022-09-29 23:59:59. I've tried this:

CREATE or replace FUNCTION date(Dt TIMESTAMP)
    RETURNS TIMESTAMP
    as
    $$select dateadd(second, -1, date_trunc('day', dateadd(day, 1, current_timestamp)))$$;

but got an error, does anyone have a clue?

Upvotes: 0

Views: 144

Answers (2)

Rajat
Rajat

Reputation: 5803

Maybe function is an overkill? Lukasz already pointed out the issue, but if you must use a function, here's another way to define the date arithmetic

select current_timestamp::date + interval '86399 seconds'

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

The code should use parameter DT and function name should be renamed as DATE already exists:

CREATE OR REPLACE FUNCTION date_(DT TIMESTAMP)
RETURNS TIMESTAMP
AS
$$select dateadd(second, -1, date_trunc('day', dateadd(day, 1, DT)))$$;

SELECT DATE_(CURRENT_TIMESTAMP());

Output:

enter image description here

Upvotes: 2

Related Questions