Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    14

    Answered: DB2 Using Date will produce wrong value

    Hi ,

    I am using DB2 9.7 LUW Enterprise edition Fixpack 4 in AIX 7.1. In the database, current date and processing using date will give wrong values. current date shows date and time. usually these shows date only..

    db2 => select current date from sysibm.sysdummy1

    1
    -------------------
    2015-09-30-14.13.21

    UTC Shows wrong values.

    db2 => select current timestamp as local, current timezone as timezone, current timestamp - current timezone as UTC from sysibm.sysdummy1

    LOCAL TIMEZONE UTC
    -------------------------- -------- --------------------------
    2015-09-30-14.12.39.309175 80000. 1796-09-17-14.12.39.309175

    Following query supposed to show day correctly. DAY is showing null.

    SELECT CURRENT DATE,
    CASE
    WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7) = 0
    THEN 'SUNDAY'
    WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7) = 1
    THEN 'MONDAY'
    WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7) = 2
    THEN 'TUESDAY'
    WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7) = 3
    THEN 'WEDNESDAY'
    WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7) = 4
    THEN 'THURSDAY'
    WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7) = 5
    THEN 'FRIDAY'
    WHEN (DAYS(CURRENT DATE) - (DAYS(CURRENT DATE)/7)*7) = 6
    THEN 'SATURDAY'
    END AS DAY
    FROM SYSIBM.SYSDUMMY1

    1 DAY
    ------------------- ---------
    2015-09-30-14.18.04 -

    How to resolve this issue ? Thanks in Advance.

  2. Best Answer
    Posted by mark.b

    "Hi,

    Seems that you have different db parameters on Prod and Dev.

    Show the result of the following commands on both systems:
    $ db2 "select substr(name, 1, 20) name, substr(value, 1, 10) value from sysibmadm.dbcfg where name like '%compat'"
    $ db2set DB2_COMPATIBILITY_VECTOR

    As for the date format, try this:
    select to_char(current date, 'YYYY-MM-DD') from sysibm.sysdummy1"


  3. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    maybe dayofweek can help
    http://www-01.ibm.com/support/knowle...10.5.0&lang=en

    The CURRENT TIMEZONE (or CURRENT_TIMEZONE) special register specifies the difference between UTC (Coordinated Universal Time, formerly known as GMT) and local time at the application server.

    The difference is represented by a time duration (a decimal number in which the first two digits are the number of hours, the next two digits are the number of minutes, and the last two digits are the number of seconds).
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Seems that you use the database in the Oracle compatibility mode.
    The following query can be used to check it:
    Code:
    select value
    from sysibmadm.dbcfg
    where name='date_compat'
    If the result is ON, then you have to use Oracle-like date arithmetic:
    Code:
    select current timestamp as local, current timezone as timezone
    , current timestamp - (
      int(substr(digits(current timezone), 1, 2))/24
    + int(substr(digits(current timezone), 3, 2))/1440
    + int(substr(digits(current timezone), 5, 2))/86400
    ) as UTC
    from sysibm.sysdummy1
    Last edited by mark.b; 09-30-15 at 08:38.
    Regards,
    Mark.

  5. #4
    Join Date
    Aug 2005
    Posts
    14
    Hi Mark & Guy Przytula ,

    Thanks for your answer. The Database is in Oracle compatible mode. But we have production and Development db servers are using DB2 9.7 FP4 in AIX 7.1 and oracle compatible mode is on in both servers.

    In Production only i am getting like this.

    db2 => select current date from sysibm.sysdummy1

    1
    -------------------
    2015-09-30-14.13.21

    db2 => select char(current_date,ISO) from sysibm.sysdummy1
    SQL0171N The data type, length or value of the argument for the parameter in
    position "1" of routine "SYSIBM.CHAR" is incorrect. Parameter name: "".
    SQLSTATE=42815


    In Develeopment i am getting correct format current date and conversion happens.

    db2 => select current date from sysibm.sysdummy1

    1
    ----------
    10/02/2015

    db2 => select char(current_date,ISO) from sysibm.sysdummy1

    1
    ----------
    2015-10-02

    I cant change the date format in production. Please advise.

  6. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Seems that you have different db parameters on Prod and Dev.

    Show the result of the following commands on both systems:
    $ db2 "select substr(name, 1, 20) name, substr(value, 1, 10) value from sysibmadm.dbcfg where name like '%compat'"
    $ db2set DB2_COMPATIBILITY_VECTOR

    As for the date format, try this:
    select to_char(current date, 'YYYY-MM-DD') from sysibm.sysdummy1
    Regards,
    Mark.

  7. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    it might be that functions are not up to date
    have you ever executed db2updv9.... ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #7
    Join Date
    Aug 2005
    Posts
    14
    Hi Mark & przytula_guy,

    Following is the result from Production DB.

    $ db2 "select substr(name, 1, 20) name, substr(value, 1, 10) value from sysibmadm.dbcfg where name like '%compat'"

    NAME VALUE
    -------------------- ----------
    number_compat ON
    varchar2_compat ON
    date_compat ON

    $ db2set DB2_COMPATIBILITY_VECTOR
    ORA


    db2 => select to_char(current date, 'YYYY-MM-DD') from sysibm.sysdummy1

    1
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2015-10-05

    following is the result from Development DB.

    $ db2 "select substr(name, 1, 20) name, substr(value, 1, 10) value from sysibmadm.dbcfg where name like '%compat'"

    NAME VALUE
    -------------------- ----------
    number_compat OFF
    varchar2_compat OFF
    date_compat OFF

    $ db2set DB2_COMPATIBILITY_VECTOR
    ORA


    db2 => select to_char(current date, 'YYYY-MM-DD') from sysibm.sysdummy1

    1
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    2015-10-05

Posting Permissions

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