Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Oracle and db2 Date issue

    This is a java code, the activity_date is stored as 01/15/2002 format.
    In Oracle there are rows updated. In db2 it returns no rows. how should I use date function so that AND AND activity_date = + "date(" + detailsDataBean.getValue("ACTIVITY_DATE") + ")"; can work.


    String tableUpdateSql = "UPDATE ap_category_list"
    + " SET category_list_name = UPPER('" + prepareStringForSql.prepareString(txtName) + "'),"
    + " descr = UPPER('" + prepareStringForSql.prepareString(txtDescr) + "'),"
    + " activity_date = current date,"
    + " activity_id = '" + userID + "'"
    + " WHERE category_list_id = " + selectTable2.getSelected()
    + " AND activity_date = "
    + "date(" + detailsDataBean.getValue("ACTIVITY_DATE") + ")";

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 always stores a column defined as “date” in an internal format that you cannot see. The 8 numbers are stored in 4 bytes (without the dashes, slashes, etc) internally within the row. The sequence of the numbers is always yyyymmdd.

    When a date is SELECTed into a 10 character variable (with the dashes, slashes, etc) the format (yyyy-mm-dd, dd/mm/yyyy, or whatever) is determined by the installation default, user exit, or can be set for that SQL statement with the CHAR function. The same applies to date values inserted, updated, or in a where clause.

    Check out the SQL reference Vol 1 for details on the CHAR function.

Posting Permissions

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