How to combine three variables into a date (MM/DD/YYYY) in Teradata-CodePudding

I have a table with the 3 below columns in Teradata and what to create a date that I can use to filter the table.

    Year Month Days
1   2,016   9   30
2   2,017   2   28
3   2,015   5   31

After the creation of a date from the 3 above columns, the 'date' table should look like so.

    Year Month Days  Date
1   2,016   9   30  9/30/2016
2   2,017   2   28  2/28/2017
3   2,015   5   31  5/31/2015

I have tried TO_DATE, different variations of Cast, etc. but it errors out.

CodePudding user response：

you can treat the result as string and concatenate all columns, only year has to be changed-

It would be better in future to save dats and times in a proper manor, as you can derive from it all columns you have, and it takes less time as to build a date

SELECT ('' || Days || '/' || Month  || '/' || CAST(oreplace(Year,',','') AS CHAR(4))  )

CodePudding user response：

Assuming the existing columns are INT/SMALLINT/BYTEINT, you can leverage Teradata's "integerdate" representation:

CAST(("year"-1900)*10000   "month"*100   "days" AS DATE) as "date"