Snowflake invalid identifier

I try to convert from SQL to SNOWFLAKE:

SET pfd = CAST(CURRENT_TIMESTAMP AS DATETIME);
SET psd = CAST(DATEADD(DAY, -30, CURRENT_TIMESTAMP) AS date);   

DECLARE curr_date DATETIME;
BEGIN
     curr_date := $psd;
     WHILE (curr_date < $pfd) DO
        INSERT INTO census_9am (census_dtm)
        VALUES (DATEADD(MINUTE, 540, curr_date));  -- 9 AM        
         curr_date := DATEADD(DAY, 1, curr_date);
    END WHILE;
END;

but I get this error all the time:

Error :invalid identifier 'CURR_DATE' (line 314)

While this dynamic SQL can be fixed, as it stands it is doing this task wrong.

You should generate the results you want and insert in one operation.

Thus given you are generating 30 days, of 9am, one option is to generate the number 1..30 and minus these from todays date, and then add 9 hour:

select 
    dateadd(hour, 9, current_date() - value::number) as result
from table(flatten(array_generate_range(1, 31)))

giving:

2025-04-23 09:00:00.000
2025-04-22 09:00:00.000
2025-04-21 09:00:00.000
2025-04-20 09:00:00.000
2025-04-19 09:00:00.000
2025-04-18 09:00:00.000
2025-04-17 09:00:00.000
2025-04-16 09:00:00.000
2025-04-15 09:00:00.000
2025-04-14 09:00:00.000
2025-04-13 09:00:00.000
2025-04-12 09:00:00.000
2025-04-11 09:00:00.000
2025-04-10 09:00:00.000
2025-04-09 09:00:00.000
2025-04-08 09:00:00.000
2025-04-07 09:00:00.000
2025-04-06 09:00:00.000
2025-04-05 09:00:00.000
2025-04-04 09:00:00.000
2025-04-03 09:00:00.000
2025-04-02 09:00:00.000
2025-04-01 09:00:00.000
2025-03-31 09:00:00.000
2025-03-30 09:00:00.000
2025-03-29 09:00:00.000
2025-03-28 09:00:00.000
2025-03-27 09:00:00.000
2025-03-26 09:00:00.000
2025-03-25 09:00:00.000

create table census_9am(census_dtm datetime);

which could be insert into the table in one operation with:

insert into census_9am 
select 
    dateadd(hour, 9, current_date() - value::number) as result
from table(flatten(array_generate_range(1, 31)))