我正在使用Snowflake门户,需要在where语句中使用sold_dt
字段作为条件。但是sold_dt
被存储为(yyyymmdd(格式的varchar
。我尝试过转换,但没有成功。我收到这个错误-SQL compilation error: error line 2 at position 8 invalid identifier 'DATETIME'
select
convert(datetime, a.sold_dt) as sold_date,
count(a.vin)
from MyTable a
where sold_date >= '2020/04/01'
group by 1
limit 100
因此,您对日期格式的设置可能与我的不同,因为我需要将WHERE子句更改为"yyyy-mm-dd"格式,但这表明它可以工作:
WITH example_data AS (
select * from values ('20200501', 'VIN_A'), ('20200501', 'VIN_B') v(sold_dt, vin)
)
select
to_date(a.sold_dt,'YYYYMMDD') as sold_date,
count(a.vin)
from example_data a
where sold_date >= '2020-04-01'
group by 1
limit 100;
给予:
SOLD_DATE COUNT(A.VIN)
2020-05-01 2