How do you UNLOAD data to S3 from Redshift and include a date in the filename

Question

I have a database table on Redshift I want to UNLOAD to S3 each month [through AWS Pipeline]. I have this code that works, but ideally I'd like to add in the current month to the filename too

UNLOAD ('
select  *
from reportingsandbox.tmp_test


')
TO 's3://reporting-team-bucket/importfiles/test_123.csv' CREDENTIALS             'aws_access_key_id=123456678;aws_secret_access_key=abcdefg' 
ALLOWOVERWRITE
delimiter ',' 
PARALLEL OFF ;

I've tried this to add in the month, but it has not worked, do you know if it is possible?

Thanks

's3://reporting-team-bucket/importfiles/test_123{month(myDateTime)}.csv' 

Answer 1

I worked it out [in aws data pipeline]!

's3://reporting-team-bucket/importfiles/test_123-#{format(@scheduledStartTime,'YYYY-MM-dd-HH')}.csv'

Thanks