SAP DATASPHERE: DYNAMIC FILTER TO START ON FIRST DAY OF THE YEAR

[MJMN]

Hello,

I am working with creating a dynamic filter from a graphic view in SAP Datasphere for a data extraction from a remote table that contains 20 million records from the source system.  I would like to apply a date filter to only extract into Datasphere the previous two years plus current to omit from extracting in the 20 million records.  

Currently I have a manual date filter of POST_DATE BETWEEN '20230101' AND '99991231'.  I would like to apply a dynamic filter so I won't have to change the dates manually each year.  I also need the date function of the filter to start on the first day of the previous two years. 

I tried subtracting the 2 years from the current date function but then the filter will start two years back based on current date.  Example:  20230212.  I need the date to be 20230101

Then for when the year switches to 2026 then the date range of the dynamic filter will be '20240101' AND '99991231'

If anyone can provide assistance that would be great. 

Thank you

[MJMN · Accepted solution]

This is what we entered to resolve the issue as a SQLScript (Table Function).

DECLARE myDate DATE = CONCAT(CAST(YEAR(CURRENT_DATE) - 2 AS VARCHAR), '0101'); DECLARE myDateString VARCHAR(8) = TO_VARCHAR(myDate, 'YYYYMMDD'); DECLARE myDateString2 VARCHAR(8) = '99991231'; return select * from "RT_ECC_ZSDT43000" where "POST_DATE" >= myDateString AND "POST_DATE" <= myDateString2 ;