'How do I combine YEAR and MONTH into a DATE?

I'm looking for an easier way to do this. My current code is written as below:

CASE WHEN LENGTH(CAST (MTH AS VARCHAR(4))) = 1 
     THEN CAST(CAST (YR AS VARCHAR(4))||'-0'||CAST (MTH AS VARCHAR(4))||'-01' AS DATE)
     ELSE CAST(CAST (YR AS VARCHAR(4))||'-' ||CAST (MTH AS VARCHAR(4))||'-01' AS DATE)
END AS RPT_MTH

The table has a field called YR with the 4 digit Year. MTH field is just a number 1 through 12, with no leading 0 for 1-9.

Is there an easier way to get to the RPT_MTH than what I'm currently doing?

sqlteradata


Solution 1:[1]

Based on Teradata's internal date:

cast((yr-1900) * 10000 + mth * 100 + 1 as date)

Solution 2:[2]

This is a little shorter:

cast(cast(YR*10000+MTH*100+1 as char(8)) as date format 'YYYYMMDD') AS RPT_MTH

Or if you need to stick to character operations:

cast(cast(YR as char(4))||right('0'||cast(MTH as varchar(2)),2) as date format 'YYYYMM')

Solution 3:[3]

Consider CONCAT, LPAD, TO_CHAR and TO_DATE which may vary in support depending on your version. Default format for TO_DATE is ISO date format at: 'YYYY-MM-DD'.

TO_DATE(CONCAT(YR, '-', LPAD(TO_CHAR(MTH), 2, '0'), '-01'))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 dnoeth
Solution 2
Solution 3 Parfait

Related Questions