Is there an equivalent way to write DATEDIFF(Week,1,[Date]) in a Snowflake query?-CodePudding

I'm new to Snowflake and attempting to translate existing SQL based queries to a Snowflake syntax. Finding some things don't easily translate. One of them being the week parameter of the SQL DATEADD function.

How does one write DATEDIFF(Week,1,[Date]) in form of a Snowflake query? Is it possible?

This question was incorrectly asked. It should have been DATEDIFF NOT DATEADD with respect to the WEEK parameter

I apologize for the misinformation. Should I change (edit) the title or delete and re-post the question properly?

This is what I'm addressing.

SELECT DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()),-3) Executes in SQL

The same query produces this error in Snowflake SQL compilation error: error line 1 at position 21 Invalid argument types for function 'DATE_DIFFTIMESTAMPINWEEKS': (NUMBER(1,0), TIMESTAMP_LTZ(9))

Similarly and a more simplified example I suppose would be:

SELECT DATEDIFF(WEEK,1,GETDATE())

CodePudding user response：

Snowflake supports INTERVAL arithmetic:

You can use interval constants to add or subtract a period of time to/from a date, time, or timestamp. Interval constants are implemented using the INTERVAL keyword, which has the following syntax:

{ | - } INTERVAL ' [ <date_time_part> ] [ , [ <date_time_part> ] ... ]'

SELECT "Date"   INTERVAL '1 WEEK'
FROM tab

CodePudding user response：

The format is:

DATEADD(Week,1,[Date])

Where the 1 signifies how many weeks you want to add

Edit: Based on the edited question, see updated answer below

I wasn't familiar with this syntax

SELECT DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()),-3)

But based on my reading and some SQL Fiddle, it seems to output the start of "this week" minus 3 days

The equivalent in Snowflake then would be:

DATEADD(DAY,-3,DATE_TRUNC(WEEK,GETDATE()))

However, taking your example literally, Snowflake would output minus 3 weeks from the start of "this week"

DATEADD(WEEK, -3, DATE_TRUNC(WEEK,GETDATE()))

CodePudding user response：

You can use the below format

select dateadd(WEEK, 1, to_date('2000-01-31')) as different_day; 
select dateadd(WEEK, 1, '2000-01-31'::date) as different_day;

Refer to the below documentation link on the usage

https://docs.snowflake.com/en/sql-reference/functions/dateadd.html#examples

CodePudding user response：

This T-SQL code is doing a truncate to week grain, and then 3 days prior.

SELECT DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()),-3)

T-SQL

SELECT d, 
    DATEADD(WEEK, DATEDIFF(WEEK,0,d),-3)
FROM (VALUES
('2022-05-19'), 
('2022-05-14'), 
('2022-05-22')
) as t(d)

gives:

d	trunc
2022-05-19	2022-05-13 00:00:00.000
2022-05-14	2022-05-06 00:00:00.000
2022-05-22	2022-05-20 00:00:00.000

So what you want to actually DATE_TRUNC

Snowflake:

SELECT d, 
    dateadd(day, -3, date_trunc(week, d))
FROM VALUES
('2022-05-19'::date), 
('2022-05-14'::date), 
('2022-05-22'::date) t(d)
;

but for my instance that is giving:

D	DATEADD(DAY, -3, DATE_TRUNC(WEEK, D))
2022-05-19	2022-05-13
2022-05-14	2022-05-06
2022-05-22	2022-05-13

Using WEEK_START session parameter we can set 7 (sunday) and it will be the same as, my current instance of SQL Server 2019..

ALTER SESSION SET WEEK_START = 7;

SELECT d, 
    date_trunc(week, d) as a,
    dateadd(day, -3, a) as b
FROM VALUES
('2022-05-19'::date), 
('2022-05-14'::date), 
('2022-05-22'::date) t(d)
;

D	A	B
2022-05-19	2022-05-15	2022-05-12
2022-05-14	2022-05-08	2022-05-05
2022-05-22	2022-05-22	2022-05-19

The sharped eyed will notice those dates don't match, I am super puzzled, in SQL server it appears 22nd May is getting "truncated" to the 23rd.. which is beyond me...

T_SQL:

SELECT d, 

    DATEADD(WEEK, DATEDIFF(WEEK,0,d),0),
    DATEADD(WEEK, DATEDIFF(WEEK,0,d),-3)
FROM (VALUES
('2022-05-19'), 
('2022-05-14'), 
('2022-05-22')
) as t(d)

input	trunc	offset
2022-05-19	2022-05-16 00:00:00.000	2022-05-13 00:00:00.000
2022-05-14	2022-05-09 00:00:00.000	2022-05-06 00:00:00.000
2022-05-22	2022-05-23 00:00:00.000	2022-05-20 00:00:00.000