Display only yyyymm of yyyymmdd field

laurastreng · #1 (**permalink**) 01-04-13, 11:48

Hi- I've looked around and not found the answer so here I am. I want to group a large amount of data and convert the date field from yyyy-mm-dd to yyyy-mm. I suppose I am extracting the year and month from the date field. I've tried DATEPART, DATE and have run out of ideas.
DATEPART(yyyymm,TMD2.D_TMR_BEGIN) AS AdmitMonth,
DATEPART(yyyymm,TMD.D_DISCHARGE) AS DischargeMonth

Thanks for the help.
Laura

ARWinner · #2 (**permalink**) 01-04-13, 11:58

What DB2 version and OS are you using. What is wrong with using YEAR and MONTH functions?

Andy

laurastreng · #3 (**permalink**) 01-04-13, 12:07

Well, apparently, having applied those two suggestions, nothing is wrong with using YEAR and MONTH :-) (I am not a db2sql expert). However, how do I apply year and month to the same date field? Now I have ADMITMONTH showing 2012 and DISCHARGEMONTH showing 1,2,etc I'd like ADMITMONTH and DISCHARGEMONTH to show 201201, 201202, 201203. Thanks.

ARWinner · #4 (**permalink**) 01-04-13, 13:10

Code:

YEAR(TMD2.D_TMR_BEGIN) AS AdmitYear,
MONTH(TMD2.D_TMR_BEGIN) AS AdmitMonth,
YEAR(D_DISCHARGE) AS DischargeYear, 
MONTH(D_DISCHARGE) AS DischargeMonth

Andy

laurastreng · #5 (**permalink**) 01-04-13, 13:35

I have this code which gives me a separate month and year for each date field: YEAR(TMD2.D_TMR_BEGIN) As "AdmitYear",
digits(cast(month(TMD2.D_TMR_BEGIN) as decimal(2))) As "AdmitMonth",
YEAR(TMD.D_DISCHARGE) As "DCYear",
digits(cast(month(TMD.D_DISCHARGE) as decimal(2))) As "DCMonth"

This gives me four columns. I would like the Year and Month to be present in one column. so 201201 for the AdmitMonthYr and 201201 for the DCMonthYr.
Is there a way to combine the two columns? I tried | | between the two but that didn't work. Thanks

Marcus_A · #6 (**permalink**) 01-04-13, 14:35

This assumes you want it as one number without dashes (contrary to your original post):

Code:

select substr(char(hiredate,iso),1,4)||substr(char(hiredate,iso),6,2) from emp

tonkuma · #7 (**permalink**) 01-04-13, 15:38

Quote:

Originally Posted by laurastreng

... I want to group a large amount of data and convert the date field from yyyy-mm-dd to yyyy-mm. ...

Code:

------------------------------ Commands Entered ------------------------------
SELECT empno
     , CHAR(hiredate , ISO)                 AS "yyyy-mm-dd"
     , SUBSTR(CHAR(hiredate , ISO) , 1 , 7) AS "yyyy-mm"
 FROM  employee
 WHERE workdept = 'A00'
;
------------------------------------------------------------------------------

EMPNO  yyyy-mm-dd yyyy-mm
------ ---------- -------
000010 1995-01-01 1995-01
000110 1988-05-16 1988-05
000120 1993-12-05 1993-12
200010 1995-01-01 1995-01
200120 2002-05-05 2002-05

  5 record(s) selected.

By the way,
you haven't answered yet to Andy's question.

Quote:

Originally Posted by ARWinner

What DB2 version and OS are you using. ...

Andy