CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Woody1193
Woody1193

Reputation: 8000

Concatening values in Snowflake

I'm trying to produce a unique value for a date so I can bucket table rows based on their date values. I tried doing this:

SELECT CONCAT(TO_VARCHAR(YEAR($1)), TO_VARCHAR(DAYOFYEAR($1))) 
FROM VALUES ('2022-01-01'), ('1972-10-01')

I would expect this to produce the following results:

Column1
1 20221
2 19721

However, I'm actually receiving an error:

Function EXTRACT does not support VARCHAR(10) argument type

I have no idea what the term, EXTRACT, is, and this appears like it should work. What am I doing wrong here?

Upvotes: 0

Views: 337

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11086

It requires a slight adjustment. As long as the date strings are YYYY-MM-DD there's no conversion required from the date strings:

SELECT CONCAT(year($1::date), DAYOFYEAR($1::date)) 
FROM VALUES ('2022-01-01'), ('1972-10-01')

However, the output is different. Since you're using DAYOFYEAR, the second row will have 275 for the day of year.

CONCAT(YEAR($1::DATE), DAYOFYEAR($1::DATE))
20221
1972275

Upvotes: 2

Related Questions