DATE Function 2

[deucesp97]

I am trying to get the date difference between a date in a DB2 column and today's date and updating it to a column.
The column type that I am updating it to is character.
I am getting an error, new value has wrong data type when trying this SQL statment.

UPDATE W17G.INVOICE
SET DAYS_N_QUEUE =
DAY(CURRENT DATE - QUEUE_DT);


Does the DAYS_N_QUEUE column need to be a date type?
[sig][/sig]

 

[deucesp97]

Is there a way to perform a date diff function in DB2? If so how? [sig][/sig]

 

[Crox]

clipped out of the ibm manual:

Example: Display the average age of employees.

SELECT AVG(YEAR(CURRENT DATE - BIRTHDATE))
FROM DSN8410.EMP;

Date Duration
A date duration represents a number of years, months, and days
expressed as a DECIMAL(8,0) number. To be properly interpreted, the
number must have the format yyyymmdd, where yyyy represents the
number of years, mm the number of months, and dd the number of days.
The result of subtracting one DATE value from another, as in the
expression HIREDATE - BIRTHDATE, is a date duration.


|--- Date Subtraction: RESULT = DATE1 - DATE2 ---------------------------|
| |
| * If DAY(DATE2) <= DAY(DATE1) |
| then DAY(RESULT) = DAY(DATE1) - DAY(DATE2). |
| |
| * If DAY(DATE2) > DAY(DATE1) |
| then DAY(RESULT) = N + DAY(DATE1) - DAY(DATE2) |
| where N = the last day of MONTH(DATE2). |
| MONTH(DATE2) is then incremented by 1. |
| |
| * If MONTH(DATE2) <= MONTH(DATE1) |
| then MONTH(RESULT) = MONTH(DATE1) - MONTH(DATE2). |
| |
| * If MONTH(DATE2) > MONTH(DATE1) |
| then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2) |
| and YEAR(DATE2) is incremented by 1. |
| |
| * YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2). |
| |
| |
|------------------------------------------------------------------------|

For example, the result of DATE('3/15/2000') - '12/31/1999' is 215 (or, a
duration of 0 years, 2 months, and 15 days).



function:


>--|-AVG function---------|------------------------------------------
|-COUNT function-------|
|-MAX function---------|
|-MIN function---------|
|-SUM function---------|
|-CHAR function--------|
|-COALESCE function----|
|-DATE function--------|
|-DAY function---------|
|-DAYS function--------|
|-DECIMAL function-----|
|-DIGITS function------|
|-FLOAT function-------|
|-HEX function---------|
|-HOUR function--------|
|-INTEGER function-----|
|-LENGTH function------|
|-MICROSECOND function-|
|-MINUTE function------|
|-MONTH function-------|
|-SECOND function------|
|-SUBSTR function------|
|-TIME function--------|
|-TIMESTAMP function---|
|-VALUE function-------|
|-VARGRAPHIC function--|
|-YEAR function--------|


and of course, DAYS_N_QUEUE must be a DATE type.

I hope this answer is helpful!

[sig][/sig]

 

[db2developer]

hi all,
while creating table one of my date column's default is to take the sysdate. how to do it

 

[K9Boxer]

I believe you need to set the column to 'Not Null w/Default'.

 

[db2developer]

how to insert java.sql.Date into db2 date