How do I combine YEAR and MONTH into a DATE?-CodePudding

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?

CodePudding user response：

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')

CodePudding user response：

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'))