Don't concatenate. It's unreadable and resource intensive.
You seem to want to get the 22nd of January of the same year or the input date.
Then, use other date functions:
TRUNC(<date>)
to truncate the date, to the year, in this case, to get to 1st January
TIMESTAMPADD()
, cast back to date, to add 22-1 days.
WITH
-- your input ...
indata(dt) AS (
SELECT DATE '2020-10-15'
UNION ALL SELECT DATE '2019-09-09'
UNION ALL SELECT DATE '2018-09-25'
)
SELECT
dt
, TIMESTAMPADD(DAY,22-1,TRUNC(dt,'YEAR'))::DATE AS convdt
FROM indata;
-- out dt | convdt
-- out ------------+------------
-- out 2020-10-15 | 2020-01-22
-- out 2019-09-09 | 2019-01-22
-- out 2018-09-25 | 2018-01-22
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…