Snowflake - Issues with table filtering on dates-CodePudding

I've created a query that produces a set of dates based on start and end date.

Everything works fine, but when I try to filter the underlying table to only produce the records between my range, it still results with all the underlying dates.

I've tried using between, changing the variables to dates using to_date(), nothing worked.

SET DATE_START_RANGE = '2018-12-1';

SET DATE_END_RANGE = CASE 
                    WHEN CURRENT_DATE = LAST_DAY(CURRENT_DATE) THEN CURRENT_DATE
                    ELSE LAST_DAY(DATEADD(MONTH,-1, CURRENT_DATE))
                    END;
                   


SELECT DATES
--,$DATE_START_RANGE
--,$DATE_END_RANGE
FROM (
      SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01') :: DATE AS DATES
      FROM TABLE(GENERATOR(ROWCOUNT => 100000)) 
      WHERE 1 =1 
      
     ) LIST_OF_DATES
WHERE 1 = 1
AND DATES >= $DATE_START_RANGE  -- this filter is not being picked up
AND DATES <= $DATE_END_RANGE  -- this filter is not being picked up
--AND DATES BETWEEN $DATE_START_RANGE AND  $DATE_END_RANGE  -- this filter is not being picked up
AND DATES LIKE '____-06-%' OR DATES LIKE '____-12-%'

Any ideas?

CodePudding user response：

The OR condition is the issue AND DATES LIKE '____-06-%' OR DATES LIKE '____-12-%':

SELECT DATES
--,$DATE_START_RANGE
--,$DATE_END_RANGE
FROM (
      SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01') :: DATE AS DATES
      FROM TABLE(GENERATOR(ROWCOUNT => 100000)) 
      WHERE 1 =1 
      
     ) LIST_OF_DATES
WHERE 1 = 1
AND DATES >= $DATE_START_RANGE  -- this filter is not being picked up
AND DATES <= $DATE_END_RANGE  -- this filter is not being picked up
AND (DATES LIKE '____-06-%' OR DATES LIKE '____-12-%')  
    -- here should be brackets around condition

More conscise way using LIKE ANY (..., ...) syntax:

SELECT DATES
FROM (
 SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY NULL), '2018-11-01')::DATE AS DATES
 FROM TABLE(GENERATOR(ROWCOUNT => 100000)) 
     ) LIST_OF_DATES
WHERE DATES BETWEEN $DATE_START_RANGE AND $DATE_END_RANGE 
AND DATES LIKE ANY ('____-06-%', '____-12-%');