How to change date format in snowflake without changing date type to varchar?

Question

I need to change only format of date without changing data type in one of the tables in snowflake, so i can't change global date format of my system. If i use this statement is showing the format by default to YYYY-MM-DD.

 to_date(allocation_date_utc) as time_employee_date,

If i use this, i change data type to varchar,so that's not what i want.

 to_varchar(allocation_date_utc, 'YYYYMMDD')

Is there any way to change the format without changing data type to varchar or char?

Answer 1

You can alter a setting named DATE_OUTPUT_FORMAT to specify how you would like dates to be displayed:

-- Displays as 2023-08-27
select to_date('2023-08-27');

alter session set DATE_OUTPUT_FORMAT = 'YYYYMMDD';

-- Now displays as 20230827
select to_date('2023-08-27');

You can set this parameter at the account, user, or session level. If you need to make the setting permanent, you can set it at the account or user level:

https://docs.snowflake.com/en/sql-reference/parameters

There is a corresponding DATE_IMPORT_FORMAT that specifies the standard input format for dates if you want to change that to YYYYMMDD without dashes.