table for date to create column-CodePudding

We are using the below code to generate the dates in snowflake.

SELECT  
DATEADD(DAY, SEQ4(), '2021-01-01') AS DATE 
FROM TABLE(GENERATOR(ROWCOUNT=>1000))

but it is not dynamic as if there are more number of dates we need to increase the ROWCOUNT any alternative for this so we can have this dynamic?

Thank you

CodePudding user response：

Where do you use it? You can use session variables for dynamic creation:

set total_days = 10000;

SELECT  
DATEADD(DAY, SEQ4(), '2021-01-01') AS DATE 
FROM TABLE(GENERATOR(ROWCOUNT=>$total_days));

Be careful: You should use ROW_NUMBER instead of SEQ4() to generate gap-free dates!!!

SELECT  
DATEADD(DAY, ROW_NUMBER() over ( ORDER BY NULL ), '2021-01-01') AS DATE 
FROM TABLE(GENERATOR(ROWCOUNT=>$total_days));

You can also generate much more than needed and then limit returning dates based on a bind (or session) variable:

SELECT  
DATEADD(DAY, ROW_NUMBER() over ( ORDER BY NULL ), '2021-01-01') AS myDATE 
FROM TABLE(GENERATOR(ROWCOUNT=> 100000 ))
WHERE myDATE < ? ;

Of course, you can also write a SP to generate the SQL statements dynamically.