Oracle to DB2 UDB Conversion Guide2003

5.17 Manipulating date and time

Both Oracle and DB2 UDB have date and time data types and functions to get the date and time from system or convert the date and time into different formats, and perform arithmetic on dates. Oracle has DATE, which can be mapped to DB2 TIMESTAMP. DB2 has two other date and time functions called DATE and TIME.

If your applications only use the date portion of Oracle's date data type, it will be more efficient to convert these types to DB2's DATE type (rather than TIMESTAMP). Here we show you some examples on date manipulation.

• Getting dates

  In Oracle, you use SELECT to get the date as following:

  SELECT sysdate from dual;

  In DB2, you use VALUES to get the date:

  SELECT current timestamp FROM sysibm.sysdummy1; SELECT current date FROM sysibm.sysdummy1; SELECT current time FROM sysibm.sysdummy1;

• Converting dates

  To convert the date in Oracle, TO_CHAR is used:

  to_char(sysdate,'YYYY-MM-DD') to_char(sysdate,'MM/DD/YYYY')

  DB2 UDB V8.1 supports functions TO_CHAR and TO_DATE but for only one format, as illustrated here:

  TO_CHAR (timestamp_expression,'YYY-MM-DD HH24:MI:SS') TO_DATE (string_expression, 'YYY-MM-DD HH24:MI:SS')

  In addition, you can use the CHAR function to convert the date and specify the localized format such as:

  CHAR(current date,ISO); CHAR(current date,USA);

  The following are more examples demonstrate how DB2 dates can be converted to different formats:

  char(current date) = '10/01/2003' char(current date + 3 days) = '10/04/2003' char(current date,ISO) = '2003-10-01' char(current date,EUR) = '01.10.2003' char(current date,JIS) = '2003-10-01' char(current time,USA) = '02:21 PM' char(current time + 2 hours,EUR) ='16.21.23'

  Note

  For more information on using CHAR function for date/time conversion, refer to DB2 UDB SQL Reference.

• Dates arithmetic

  Dates arithmetic is frequently used. The following is an Oracle example:

  add_months(sysdate,16)

  In DB2, the similar function can be implemented as following:

  current date + 16 months

  Here are other examples of how arithmetic manipulation can be done with DB2 dates:

  current date = 10/02/2003 current date + 3 days = 10/05/2003 current timestamp + 2 years = 2005-10-02-12.33.27.667000 current timestamp - 2 months = 2003-08-02-12.33.27.667002 current time + 5 minutes = 12:38:27

  DB2 also provides some more functions to manipulate with dates, such as days, dayname, monthname, and much more.

• Using UDF

  Certain Oracle functions not supported by DB2 can be easily duplicated by writing UDF. For example, the following UDF can be used to convert the Oracle built-in function last_day:

  CREATE FUNCTION last_day(v_date date) RETURNS DATE SPECIFIC lastday LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN (v_date + 1 MONTHS) - DAY(v_date + 1 MONTHS) DAYS;

  You can find the UDF samples to convert Oracle DUMP(date), NEW_TIME, NEXT_DAY(), TRUNC() and other sample UDFs for migration at IBM Web site:

  http://www7b.software.ibm.com/dmdd/library/samples/db2/0205udfs/

  Another example of using DB2 UDF in dates arithmetic is converting the Oracle months_between function:

  months_between(sysdate,v_date)

  If you use MTK to automate your conversion, MTK will implement months_between as a User Defined Function and automatically deploy it into the database. The following is the source code for this function:

  CREATE FUNCTION months_between(d1 TIMESTAMP, d2 TIMESTAMP) RETURNS FLOAT LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL RETURN 12*(year(d1) - year(d2)) + month(d1) - month(d2) + (TIMESTAMPDIFF(2,CHAR(d1 - (d2 + (12*(year(d1) - year(d2)) + month(d1) - month(d2)) MONTHS))) / 2678400.0)

  This function employs the DB2 built-in function TIMESTAMPDIFF. Details for TIMESTAMPDIFF can be found in the DB2 UDB manual SQL Reference.

For more information on manipulation with dates, refer to the article on the DB2 developer domain:

http://www7b.software.ibm.com/dmdd/library/techarticle/0211yip/0211yip3.html