How to fetch month and year at the same time from date where date column is in varchar datatype. Using snowflake tool

user_15369200 0
Viewed 220

I am trying to fetch both the months and years from the Column (MISSUE) while introducing an "_" in snowflake. For example (6_2021, 7_2021 etc).

This can be archive in MS Access query as:

Month([MISSUE]) & "_" & Year([MISSUE]) AS Month_Year

My current Code in Snowflake shows:

EXTRACT(Month, TO_DATE(MISSUE)) &'_'& EXTRACT(Year, TO_DATE(MISSUE)) AS Month_Year

Can someone please tell me what I am during wrong?

Thank you!

snowflake-cloud-data-platform
2 Answers

Alternatively save yourself some typing (dad joke) :

select to_char(MISSUE::DATE,'MM_YYYY') from tbl

enter image description here

Copy|Paste|Run|In Snowflake:

with tbl as (select $1 as MISSUE from values ('2017-04-10'), ('2017-06-10'))
select
    to_char(MISSUE :: DATE, 'MM_YYYY') cleaner
from
     tbl
user_13524900

You can use CONCAT_WS() for this.

with tbl as (select $1 as MISSUE from values ('2017-04-10'), ('2017-06-10'))

select concat_ws('_', month(to_date(MISSUE)), year(to_date(MISSUE))) from tbl;

CONCAT_WS('_', MONTH(TO_DATE(MISSUE)), YEAR(TO_DATE(MISSUE)))
4_2017
6_2017
user_20199780
Related