更改Snowflake中值的格式

Question

我是sql的新手，我想知道是否可以格式化这些值在它们各自的列中的外观。例如，我正在创建一个名为FLATJSON2的表，格式如下

create or replace table flatjson2 as select 
 value:"AccountID"::varchar as AccountID,
 value:"AvgMarginPerEE"::varchar as AvgMarginPerEE,
 value:"CloseDate"::timestamp as CloseDate,
 value:"CloseReason"::varchar as CloseReason,
 value:"CloseWonLoss"::varchar as CloseWonLoss,
 value:"Name"::varchar as Name,
 value:"NewOrRepeatCustomer"::varchar as NewOrRepeatCustomer,
 value:"OpportunityAmount"::int as OpportunityAmount,
 value:"OpportunitySource"::varchar as OpportunitySource,
 value:"OpportunityStage"::varchar as OpportunityStage,
 value:"ProductCount"::int as ProductCount,
 value:"SetUpFee"::varchar as SetUpFee,
 value:"AGFeeRate"::int as AGFeeRate,
 value:"pyDateValue"::timestamp as CreationDate,
 value:"pyDecimalValue"::int as AnnualSalary,
 value:"pyID"::varchar as CaseID,
 value:"pyOwnerUserID"::varchar as OwnerUserID,
 value:"pzInsKey"::varchar as pzInsKey,
 value:"pxPages":"crmIndexProducts":"EstimatedEmployerBurden"::int as EstimatedEmployerBurden,
 value:"pxPages":"crmIndexProducts":"EstimatedMargin"::int as EstimatedMargin,
 value:"pxPages":"crmIndexProducts":"MonthlySalary"::int as MonthlySalary,
 value:"pxPages":"crmIndexProducts":"OppProductID"::varchar as OppProductID,
 value:"pxPages":"crmIndexProducts":"AGFee"::int as AGFee,
 value:"pxPages":"crmIndexProducts":"CountryID"::int as Country

 from snowtable, lateral flatten (input =>jsontext);

我正在尝试使日期列的格式为1/30/2019和货币列的这种格式$2,000。

当我创建表格的时候，我能完成吗？

Answer 1

您可以使用parameters更改Snowflake日期格式，这可能会显示在Snowflake Web GUI中，但不会以任何方式导出。

要将转换硬编码为VARCHAR，您可以使用例如：

SELECT TO_CHAR('2019-01-30'::DATE, 'MM/DD/YYYY') DT, TO_CHAR(2000, '$MI999,999,999');

Snowflake只有两位数的日期和月份。