Search code examples
sqldateconcatenationvertica

Concatenation in SQL Vertica to get year from date column?

I have column in SQL Vertica table like below:

date_col
2020-10-15
2019-09-09
2018-09-25

and I try to do something like this: select TO_DATE('22-01'|| YEAR("date_col"::varchar(4)),'DD-MM-YYYY') from table

so as to have result like:

2020-01-22
2019-01-22
2018-01-22

Nevertheless, when I try my code i have bad result like for example:

3570-03-02 and so on...

maybe you know better solution than my code so as to achieve what I need?

Solution

  • 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