Returning data within a date range in Snowflake gives error-CodePudding

I am using the strings below in Snowflake, as I have previously used in SQL Server, to get a date range and I am getting this error

SQL compilation error: error line 3 at position 48 Invalid argument types for function 'DATE_DIFFTIMESTAMPINMONTHS': (NUMBER(1,0), TIMESTAMP_LTZ(9))

What I want to achieve is to get the date from 31/05/2022 to 01/05/2022 in column "assign Date (Local time)" from "YML"."SYNCASSIGN"."uio_faster_daily" table in Snowflake.

I agree the syntax in Snowflake might be slightly different from SQL Server, but I feel it just requires a small adjustment which I am also trying.

SELECT *
FROM "YML"."SYNCASSIGN"."uio_faster_daily"
where "assign Date (Local time)" >=DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0) 
and
"assign Date (Local time)" < DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0))

CodePudding user response：

You can get the last day of a month using the LAST_DAY function.

You can get the first day of the month using this:

date_trunc('month', current_date())

If you need to get a date in the previous month (compared to the current date) to plug into these two functions then you can use:

add_months(current_date(), -1)

CodePudding user response：

I have to say I didn't test it properly, but you should be able to get the beginning of the previous month with the following expression:

select DATE_TRUNC('month', DATEADD(month, -1 , getdate()))::DATE ;

The following should give you the last day of the previous month:

select LAST_DAY(DATEADD(month, -1 , getdate())) ;

So you should be able to write your query like this:

SELECT *
FROM "YML"."SYNCASSIGN"."uio_faster_daily"
where "assign Date (Local time)" >= DATE_TRUNC('month', DATEADD(month, -1 , getdate()))::DATE
and
"assign Date (Local time)" < LAST_DAY(DATEADD(month, -1 , getdate()));

If you want to include the last day, the last filter should be "<=" instead of "<".