Snowflake table with column 'CURRENT_DATE' - how to use the column in where clause - Stack Overflow

admin管理员组

文章数量:1131183

I have somehow ended up with a table in our datalake where a column name is "CURRENT_DATE"

Snowflake allows to create

    create or replace TABLE TEST (
    "current_date" DATE
);

Trying to use the below query to create the table errors out

 create or replace TABLE TEST (
        "CURRENT_DATE" DATE
    );

However the below query does allow to end with a column "CURRENT_DATE" in upper case in a table in snowflake

  create or replace TABLE TEST as 
  select Col1 as "CURRENT_DATE" from TEST1

Now I am having a hard time figuring out how to use this field in WHERE clause or CASE statement.

select * from TEST where "CURRENT_DATE" ='2027-10-01' limit 10;

It is not able to query properly

Is there a way to query on this field? If not, why does snowflake even allow to create a column with the name "CURRENT_DATE" !

I have somehow ended up with a table in our datalake where a column name is "CURRENT_DATE"

Snowflake allows to create

    create or replace TABLE TEST (
    "current_date" DATE
);

Trying to use the below query to create the table errors out

 create or replace TABLE TEST (
        "CURRENT_DATE" DATE
    );

However the below query does allow to end with a column "CURRENT_DATE" in upper case in a table in snowflake

  create or replace TABLE TEST as 
  select Col1 as "CURRENT_DATE" from TEST1

Now I am having a hard time figuring out how to use this field in WHERE clause or CASE statement.

select * from TEST where "CURRENT_DATE" ='2027-10-01' limit 10;

It is not able to query properly

Is there a way to query on this field? If not, why does snowflake even allow to create a column with the name "CURRENT_DATE" !

• snowflake-cloud-data-platform

Share Improve this question Follow edited Jan 8 at 0:37 Nishant Shrivastava asked Jan 7 at 23:26 Nishant ShrivastavaNishant Shrivastava 52333 silver badges1212 bronze badges 6

• current_date is a reserved Snowflake keyword. But as you escaping it in double quotes, the select query you pasted above actually should be working. – Tim Biegeleisen Commented Jan 7 at 23:34
• please do not store date values in TEXT fields. You'll regret it later if you do. Use DATE or DATETIME. – D Stanley Commented Jan 7 at 23:39
• Hello Stannley, I am not storing date as TEXT.. this was just a sample code incase somebody wanted to try it. – Nishant Shrivastava Commented Jan 7 at 23:44
• @TimBiegeleisen The query is not working...thats what is frustrating, – Nishant Shrivastava Commented Jan 7 at 23:45
• @TimBiegeleisen / @D Stanley - i have updated the question with some more information .. – Nishant Shrivastava Commented Jan 8 at 0:41

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 0

(this could have went in a comment, but I do not have enough reputation for posting one)

Odd, the provided query does work for me out of the box. Do you happen to have QUOTED_IDENTIFIERS_IGNORE_CASE set to TRUE (try SHOW PARAMETERS LIKE 'QUOTED_IDENTIFIERS_IGNORE_CASE')? Either way, qualifying the table name does allow me to retrieve the value in the column regardless of that:

select * from TEST t where t."current_date" ='2027-10-01' limit 10;

As to why Snowflake would let you create a table with such a field, it is because Snowflake's SQL is case-sensitive but case-converting by default, and only the all-uppercase name is actually reserved. Attempting to create a table with a column named 'CURRENT_DATE' through the DDL appears to error out as expected, with something like

invalid column definition name 'CURRENT_DATE' (ANSI reserved)

If you somehow ended up in that situation anyway I'm afraid this won't be of much help.

create table so_test(id number, current_date date);

Error: .invalid column definition name 'CURRENT_DATE' (ANSI reserved) (line 1)

Wowsers, could have taken the hint to use a different word.

So if we then uses double quotes, we can create with the name being lower case (as you noted):

create table so_test(id number, "current_date" date);

insert into so_test values 
    (1, '2024-12-03'::date),
    (2, '2025-01-06'::date),
    (3, '2025-02-24'::date);

and can query this (again as you noted):

select * 
from so_test 
where "current_date" >= '2025-01-01'::date;    

so changing to upper case like you noted, gives and error:

create table so_test2(id number, "CURRENT_DATE" date);

Error: .invalid column definition name 'CURRENT_DATE' (ANSI reserved) (line 10)

so working around this, to make a thing the DB does not want you let to do... as you note...

create table so_test2 as
  select 
    id+10 as id, 
    "current_date" as "CURRENT_DATE"
  from so_test;

select * from so_test2;

so now you do the last test:

select * 
from so_test2
where "CURRENT_DATE" >= '2025-01-01'::date;

gives all results, because "CURRENT_DATE" is CURRENT_DATE that reserved word, the DB tired to help you avoid..

So, now you only hope is to used the column index.. this will be so fragile:

select * from so_test2
where $2 >= '2025-01-01'::date;

本文标签： Snowflake table with column 39CURRENTDATE39how to use the column in where clauseStack Overflow