DB2 date conversion

问题:

I have 2 INTEGER columns like the following:

Month      Year
-----      -----
  5         2011

Is there any way to convert that to a single column VARCHAR like this: May-2011

回答1:

I don't know of an easy way to do this since you don't have a date object (ie its not like youre finding the month of a timestamp), you can use a case statement but it gets long.

SELECT CASE Month
            WHEN '1' THEN 'January'
            WHEN '2' THEN 'February'
            WHEN '3' THEN 'March'
            WHEN '4' THEN 'April'
            ...
      END+'-'+Year
FROM TABLE

回答2:

I think this will do it:

SELECT
MONTHNAME(
  DATE(CAST(Year AS CHAR(4)) || '-' || TRIM(CAST(Month AS CHAR(2))) || '-1')
) || '-' || CAST(Year AS CHAR(4))
FROM TABLE

回答3:

This should do the trick, assuming that the columns Month and Year are integers and Month has the domain 1-12:

select   substring('---JanFebMarAprMayJunJulAugSepOctNovDec', 3*Month , 3 )
       + '-'
       + right(digits(Year),4)
from some_table

If Month is 0 you'll get '---' as the month; if it's less than 0 or greater than 12, you'll get some sort of blooey.

回答4:

You could create a function to convert the month value, like this...

CREATE FUNCTION INT2MONTH (MONTH INTEGER)
     RETURNS VARCHAR(100)
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
     RETURN MONTHNAME('2000-' || RIGHT('0' || STRIP(CHAR(MONTH)), 2) || '-01')

Then you can...

select int2month(month) || '-' || strip(char(year)) from test

1                                     
--------------------------------------------------
May-2011                                                          
June-2011                                                         
December-2012                                                     

If you want a 3 char month then change last last on function to...

RETURN LEFT(MONTHNAME('2000-' || RIGHT('0' || STRIP(CHAR(MONTH)), 2) || '-01'), 3)

回答5:

I realize this question is pretty old, but there's a way that is a lot simpler than any of the options listed here (in my opinion) -- a combination of some date math and the VARCHAR_FORMAR() function:

SELECT 
    VARCHAR_FORMAT(
         DATE('0001-01-01') + (month_col - 1) MONTH + (year_col - 1) YEAR
        ,'Month-YYYY'
    )
FROM your_table