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:

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?

CodePudding user response：

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.