Db2

Db2

Connect with Db2, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Different date subtraction between 10.5 and 11.5

    Posted Tue May 05, 2020 04:29 PM
    Hi community,

    I am facing this issue, but I am not sure if this is right or did I miss to set something.

    I am just simply performing a date subtraction without specifying the type (day, hour, minute), and I got the following (I executed on 2020-05-04):

    v11.5 - db2 supposes it is a day

    [db2inst1@db2v115 ~]$ db2 "values current date - 1"
    1
    ----------
    05/03/2020
     
      1 record(s) selected.
     
    [db2inst1@dbv115 ~]$ db2 "values current timestamp - 1"
     
    1
    --------------------------
    2020-05-03-12.07.01.750541
     
      1 record(s) selected.

    v10.5 - it throws an error  

    [db2inst1@db2v105 ~]$ db2 "values current date - 1 "
    SQL0182N  An expression with a datetime value or a labeled duration is not
    valid.  SQLSTATE=42816
    [db2inst1@db2v105 ~]$ db2 "values current timestamp - 1"
    SQL0182N  An expression with a datetime value or a labeled duration is not
    valid.  SQLSTATE=42816


    ------------------------------
    Andres Gomez Casanova
    IBM Champion 2015-2020
    DBA senior
    Experian
    Bogota
    ------------------------------

    #Db2


  • 2.  RE: Different date subtraction between 10.5 and 11.5

    Posted Tue May 05, 2020 04:55 PM
    Hello Andres,

    Long time since we've spoken (back in the DB2UNIT days).

    Take a look at this page from the Knowledge Center -


    Then compare it to the V10.5 page of the same information.

    The subtle difference, introduced at V11.1 level, is that there now are default values for the text component of the second clause of a date or time arithmetic.   For arithmetic involving dates this is "DAYS" and for arithmetic involving time this is "SECONDS".

    This is seem in the KC page by the little suffixes "1" and "2" on the DAY and SECOND components.

    So "CURRENT DATE - 1" is equivalent to "CURRENT DATE - 1 DAY".

    And "CURRENT TIMESTAMP - 1" is equivalent to "CURRENT TIMESTAMP - 1 SECOND"

    I hadn't noticed this before, but your question intrigued me and so I went to look.

    Not that I'd think of using this anytime soon.   I suspect it was brought in for compatibility with one of the other products Db2 is trying to emulate.

    HTH

    Phil



    Original Message


  • 3.  RE: Different date subtraction between 10.5 and 11.5

    Posted Fri May 08, 2020 02:07 AM

    Hi Andres, hi Phil,

    thanks for the lesson! Didn't know this tiny difference in these two versions.

    But in general: Always be specific what you want. Never let the computer decide in ambiguous statements and argument type checking or promotion.
    It is one of the lessons you'll learn while programming C (=long time ago!). It just can go wrong sooner or later.

    And if you find an old copy of Graeme Birchall's excellent Db2 Cookbook, have a look at the chapter "Quirks in SQL" and all time/date related sections.

    Lot's of food for thought in this part

    Cheers
    Roland



    ------------------------------
    Roland Schock
    Distinguished Engineer
    ARS Computer und Consulting GmbH
    Muenchen
    ------------------------------

    Original Message


  • 4.  RE: Different date subtraction between 10.5 and 11.5

    Posted Thu May 07, 2020 12:33 AM
    Hi Andres,
    Is Date compatibility setting OFF in 10.5?
    If Date compatibility setting is ON, it can be used with the corresponding syntax in 10.5.
    On the other hand, in 11.5, the expression can be used even if the Date compatibility setting is OFF.
    When upgrading from 10.5 to 11.5. It seems that the application needs to be changed. It's hard to find an explanation for this change in the knowledge center.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


Related Content