variablessnowflake-cloud-data-platform

How to provide sysdate pattern in Copy file command in Snowflake

I have below Copy Files command in Snowflake which is working fine. But currently the date 20240822 is hardcoded in this pattern and i want to provide sysdate so that it can copy the files with sysdate folder daily.

COPY FILES
INTO @STG_RAW_DPT_UPLOAD_ARCHIVE
FROM @STG_RAW_DPT_UPLOAD
PATTERN = '^dpt20240822.*'
Solution

  • If your copy command fits under 256 characters, you could store that to a session variable and execute that instead

    set stmt = 'copy files into @that_stage from @this_stage pattern='||$$'$$||to_char(current_date,'.*dptyyyymmdd.*'||$$'$$);

select $stmt;

execute immediate $stmt;