Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    31

    Display only yyyymm of yyyymmdd field

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,130
    What DB2 version and OS are you using. What is wrong with using YEAR and MONTH functions?

    Andy

  3. #3
    Join Date
    Jul 2012
    Posts
    31
    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.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,130
    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

  5. #5
    Join Date
    Jul 2012
    Posts
    31
    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

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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
    Last edited by Marcus_A; 01-04-13 at 15:54.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,478
    Quote Originally Posted by laurastreng View Post
    ... 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 View Post
    What DB2 version and OS are you using. ...

    Andy
    Last edited by tonkuma; 01-04-13 at 16:11. Reason: Replace TO_CHAR with CHAR(... , ISO)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •