Need to create a column name which displays current month end

Question

I have a requirement to create a column name in snowflake such that it displays the current month end date. It should change dynamically every month.

Expected table structure.

Tablename:- sample
Column names :- 
1.Name - Varchar field
2.2023-01-31 (Should be Current monthend) - Integer field.

When I do a select * from sample NEXT month , the column names should be as below :

Name	2023-02-29
A1	15876
B3	8976

I have tried something like this using identifier, and it is throwing the below error in the create table statement.

"Unexpected '('.syntax error line 5 at position 0 unexpected ')'.

Set NEW123=(to_varchar(Current_Date(),'YYYY-MM-DD');

CREATE TABLE SAMPLE
(
IDENTIFIER($NEW123) INTEGER
);

Answer 1

so the SQL you wish the results to be would be the equivalent of:

create table "SAMPLE" (name text, "2023-02-29" number);

the table name needs to be in quotes because sample is a reserved word.

using LAST_DAY we can get the month end:

select current_date() as cd
    ,LAST_DAY(cd, 'month') as end_date;

CD	END_DATE
2024-01-12	2024-01-31

so now we just need to build the SQL we want:

select current_date() as cd
    ,LAST_DAY(cd, 'month') as end_date
    ,'create table "SAMPLE" (name text, "'|| LAST_DAY(cd, 'month')::text ||'" number)' as sql;

CD	END_DATE	SQL
2024-01-12	2024-01-31	create table "SAMPLE" (name text, "2024-01-31" number)

lovely, lovely, now lets run that via EXECUTE IMMEDIATE

DECLARE
    sql text;
BEGIN
    sql := 'create table "SAMPLE" (name text, "'|| LAST_DAY(current_date(), 'month')::text ||'" number)';

    EXECUTE IMMEDIATE :sql;
END;

select * from "SAMPLE";