1

I have table ABC in which I have column Z of datatype Date. The format of the data is YYYYMMDD. Now I am looking to convert the above format to YYYY-MON-DD format. Can someone help?

Mohan krishna Bellamkonda
  • 23
  • 1
  • 6
  • If column z is defined as a date in SF, it should not have format YYYYMMDD. Is it defined as a varchar? – Mike Gohl Sep 08 '20 at 16:06
  • Column Z is defined as Number(38,0). One another question : what would be the format if the column is defined as date? – Mohan krishna Bellamkonda Sep 08 '20 at 16:09
  • Snowflake has a couple of parameters that determine what format you see or what format Snowflake expect. DATE_OUTPUT_FORMAT and DATE_INPUT_FORMAT. The parameter have nothing to do with how the date is stored internally. – Mike Gohl Sep 08 '20 at 17:05
  • What is the difference between To_char(to_date(To_char(z),'YYYYMMDD'),'YYYY-MON-DD). and. to_date(z,'YYYY-MON-DD'). The first logic works perfectly whereas the second one doesn't work. Why is this. The underlying column Z is of date data type and the preference is set to YYYYMMDD. – Mohan krishna Bellamkonda Sep 08 '20 at 17:38
  • If z is defined as number, you will get an error when you try to_date(z,'YYYY-MON-DD'). – Mike Gohl Sep 08 '20 at 17:46

4 Answers4

0

You can use to_char

TO_CHAR(Z,'YYYY-MON-DD')
hopeIsTheonlyWeapon
  • 557
  • 6
  • 19
0

Depending on what the purpose of the reformatting is, you can either explicitly cast it to a VARCHAR/CHAR and define the format, or you can change your display format to however you'd like to see all dates:

ALTER SESSION SET DATE_OUTPUT_FORMAT = 'YYYY-MON-DD';

It's important to understand that if the data is in a DATE field, then it is stored as a date, and the format of the date is dependent on your viewing preferences, not how it is stored.

Mike Walton
  • 6,595
  • 2
  • 11
  • 22
  • What is the difference between To_char(to_date(To_char(z),'YYYYMMDD'),'YYYY-MON-DD). and. to_date(z,'YYYY-MON-DD'). The first logic works perfectly whereas the second one doesn't work. Why is this. The underlying column Z is of date data type and the preference is set to YYYYMMDD. – Mohan krishna Bellamkonda Sep 08 '20 at 17:35
  • It doesn't sound like the column z is actually a date data type. Can you confirm? – Mike Walton Sep 08 '20 at 17:55
0

Since the value of the date field is stored as a number, you have to convert it to date.

ALTER SESSION SET DATE_OUTPUT_FORMAT = 'YYYY-MON-DD';

select to_date(to_char( z ), 'YYYYMMDD');

Mike Gohl
  • 627
  • 4
  • 7
0

(adding this answer to summarize and resolve the question - since the clues and answers are scattered through comments)

  • The question stated that column Z is of type DATE, but it really seems to be a NUMBER.
  • Then before parsing a number like 20201017 to a date, first you need to transform it to a STRING.
  • Once the original number is parsed to a date, it can be represented as a new string formatted as desired.
WITH data AS (
  SELECT 20201017 AS z
)

SELECT TO_CHAR(TO_DATE(TO_CHAR(z), 'YYYYMMDD'), 'YYYY-MON-DD')
FROM data;

# 2020-Oct-17
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325