Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Unanswered: Inserting/updating Dynamic Date values (‘YYYY/MM’ / ‘YYYY-MM-01’ ,‘MM/01/YYYY’ )

    Hello,

    I am trying to write some DB2 queries to do following activity. Can you help me with you suggestions..

    A). I need to INSERT a date in the format ‘YYYY-MM-01’ to the date field where YYYY is current year and MM is current month + 1.

    Example, for today(07/18/13) it would be ‘2013-08-01’

    B). I need to write a UPDATE query with SET date as ‘MM/01/YYYY’ format where YYYY is current year and MM is current month + 1.

    Example, for today(07/18/13) it would be ‘08/01/2013’

    C). I need to write SELECT query with WHERE clause as ‘YYYY/MM’ format where YYYY is current year and MM is current month.

    Example, for today(07/18/13) it would be ‘2013/07’

    Any suggestions would be appreciated. Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by mssrone View Post
    ...

    A). I need to INSERT a date in the format ‘YYYY-MM-01’ to the date field where YYYY is current year and MM is current month + 1.

    Example, for today(07/18/13) it would be ‘2013-08-01’
    Some examples might be...
    current_date - (DAY(current_date) - 1 ) DAYS + 1 MONTH
    or
    LAST_DAY(current_date) + 1 DAY


    B). I need to write a UPDATE query with SET date as ‘MM/01/YYYY’ format where YYYY is current year and MM is current month + 1.

    Example, for today(07/18/13) it would be ‘08/01/2013’
    Format of the string representation for a DATE datatype is not significant.
    Internal format for a DATE datatype is always "unsigned decimal with format 'yyyymmdd' ".

    So, same expressions as A) like ...
    current_date - (DAY(current_date) - 1 ) DAYS + 1 MONTH
    or
    LAST_DAY(current_date) + 1 DAY

    would be applicable.


    C). I need to write SELECT query with WHERE clause as ‘YYYY/MM’ format where YYYY is current year and MM is current month.

    Example, for today(07/18/13) it would be ‘2013/07’
    Please try...
    Code:
     WHERE column_a
           BEWEEN current_date - (DAY(current_date) - 1) DAYS
              AND LAST_DAY(current_date)
    or
    Code:
     WHERE TO_CHAR(column_a     , 'yyyy/mm')
         = TO_CHAR(current_date , 'yyyy/mm')

  3. #3
    Join Date
    Jul 2013
    Posts
    3
    Thanks Tonkuma.

    A worked well.

    For B,

    current_date - (DAY(current_date) - 1 ) DAYS + 1 MONTH
    or
    LAST_DAY(current_date) + 1 DAY

    Column is updated with '2013-08-01' but I am expecting ‘08/01/2013’ .
    Column data type is V(50).

    For C, getting errors as below..

    Option 1:-
    WHERE column_a
    BEWEEN current_date - (DAY(current_date) - 1) DAYS
    AND LAST_DAY(current_date)


    SQLCODE = -401, ERROR: THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE
    SQLSTATE = 42818 SQLSTATE RETURN CODE
    SQLERRP = DSNXOBFC SQL PROCEDURE DETECTING ERROR
    SQLERRD = 936 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'000003A8' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'

    Option = 2 :-
    WHERE TO_CHAR(column_a , 'yyyy/mm')
    = TO_CHAR(current_date , 'yyyy/mm')


    SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
    SQLSTATE = 56038 SQLSTATE RETURN CODE
    SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
    SQLERRD = -666 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'FFFFFD66' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'
    INFORMATION

    My column data type is C(7) .

    Could you please help..
    Thanks.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would use the substr function for these. You may need to use the CHAR function also to specify that the date is always returned in ISO, USA, etc format so you know how to substr it.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by mssrone View Post
    ...

    For B,

    current_date - (DAY(current_date) - 1 ) DAYS + 1 MONTH
    or
    LAST_DAY(current_date) + 1 DAY

    Column is updated with '2013-08-01' but I am expecting ‘08/01/2013’ .
    Column data type is V(50).
    The datatype of your column was not a DATE(You wrote that "Column data type is V(50)" and you are expecting ‘08/01/2013’).

    Please see my previous post, in that I worte
    Format of the string representation for a DATE datatype is not significant.
    Internal format for a DATE datatype is always "unsigned decimal with format 'yyyymmdd' ".
    Anyway,
    you might be NOT working with DATE datatype.
    So, please try the TO_CHAR function, like...
    Code:
    TO_CHAR(current_date - (DAY(current_date) - 1 ) DAYS + 1 MONTH , 'mm/dd/yyyy')
    or
    TO_CHAR(LAST_DAY(current_date) + 1 DAY , 'mm/dd/yyyy')

    For C, getting errors as below..

    Option 1:-
    Code:
    WHERE column_a
           BEWEEN current_date - (DAY(current_date) - 1) DAYS
              AND LAST_DAY(current_date)
    SQLCODE = -401, ERROR: THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE
    SQLSTATE = 42818 SQLSTATE RETURN CODE
    SQLERRP = DSNXOBFC SQL PROCEDURE DETECTING ERROR
    SQLERRD = 936 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'000003A8' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'

    Option = 2 :-
    Code:
    WHERE TO_CHAR(column_a     , 'yyyy/mm')
         = TO_CHAR(current_date , 'yyyy/mm')
    SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
    SQLSTATE = 56038 SQLSTATE RETURN CODE
    SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
    SQLERRD = -666 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'FFFFFD66' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'
    INFORMATION

    My column data type is C(7).

    Could you please help..
    Thanks.
    The text " BEFORE NEW FUNCTION MODE ..." in your error message might suggest that you are using DB2 for z/OS.
    Would you please publish DB2 version/release/fixpack and platfor OS which you are using?


    "My column data type is C(7)"
    Are you serious?
    I don't know data type C(7).
    If you insist working with C(7),
    would you supply the link to the description or the documentations for that?
    I guessed that you might be working with CHAR(7) or VARCHAR(7).
    (Again, I don't know dataype with C(7) ).
    Please describe exactly what you were working.
    Copy and paste the script which you were doing might be strongly recommended.

  6. #6
    Join Date
    Jul 2013
    Posts
    3
    Thanks Marcus and Tonkuma.

    I am working on mainframe DB2, It's version DB2 V10.

    I have used short cut for format, what I mean C(7) is CHAR(7).
    Sorry for the confusion.

    Got some improvement.

    For C). SELECT query with WHERE clause as ‘YYYY/MM’ ,

    I have used below query and then it worked fine.

    substr(YR_MO,1,4) = varchar_format(current timestamp, 'yyyy')
    AND substr(YR_MO,6,2) = varchar_format(current timestamp, 'MM')

    But still having issue with B.

    For B) UPDATE query with SET date as ‘MM/01/YYYY’ format

    I tried below options.

    TO_CHAR(current_date - (DAY(current_date) - 1 ) DAYS + 1 MONTH , 'mm/dd/yyyy')
    or
    TO_CHAR(LAST_DAY(current_date) + 1 DAY , 'mm/dd/yyyy')

    getting same error.

    SQLCODE = -4700, ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE

    Then I changed my query as below.

    RTRIM(CHAR(MONTH(current_date) + 1))
    || '/' || '01' || '/' || CHAR(year(current_date))

    It's working fine but '0' is supressing in month, it's displaying as
    ‘8/01/2013’ but need "‘08/01/2013’ ".

    Any suggestions on B to get '08' for month?

    Thanks.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    RTRIM(CHAR(MONTH(current_date) + 1))
    || '/' || '01' || '/' || CHAR(year(current_date))

    It's working fine but '0' is supressing in month, it's displaying as
    ‘8/01/2013’ but need "‘08/01/2013’ ".

    Any suggestions on B to get '08' for month?
    You might already realized that most of your issues were caused by using VARCHAR(50) for DATE data.
    My first recommendation may be to use DATE datatype for DATE data.

    But, sometimes it might be not so easy to alter the datatype for a column.
    In that case, please consider Marcus_A's suggestion like...
    ... You may need to use the CHAR function also to specify that the date is always returned in ISO, USA, etc format so you know how to substr it.
    along to the lines, an idea might be ...
    CHAR(current_date - (DAY(current_date) - 1) DAYS , USA)


    Here is a result tested on DB2 9.7 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CHAR(current_date , ISO) AS current_date
         , CHAR(current_date - (DAY(current_date) - 1) DAYS , USA) AS issue_b
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    CURRENT_DATE ISSUE_B   
    ------------ ----------
    2013-07-20   07/01/2013
    
      1 record(s) selected.

Posting Permissions

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