DB2 SQl statement

RaghuING · #1 (**permalink**) 01-18-08, 07:59

Hi all,

I have a table with a column Frequency which is deimal of two digits, indicates the number of months in decimal (36 Months). i have to build a query in such a way that i have to deduct from the system date.

Example:

Select X,Y,Z from Table where End date < (System date - Frequency)

i have to convert 36 months to a date from the system date.

can you help me out in this regard.

Rgds
Raghu

ARWinner · #2 (**permalink**) 01-18-08, 08:02

select x,y,z from table where end_date < (current date - frequency months)

Andy

RaghuING · #3 (**permalink**) 01-18-08, 10:45

Hi andy,

i want to convert the frequency months to a date(dd/mm/yyyy) format subtracting from the Current date.

Awaiting for your reply.

Rgds
Raghu

ARWinner · #4 (**permalink**) 01-18-08, 10:51

That is what I gave you, or I do not understand what you want.

Andy

RaghuING · #5 (**permalink**) 01-31-08, 04:24

Hi andy,

I tried your sql statement it is not working....

Query that i have tried:

SELECT VW450.LAR_ID,
VW450.ACNT_ID,
VW450.BEG_DT,
VW450.EIND_DT,
VW455.PURGE_FREQ
FROM FPOIS912.VW450_LAR_OVK AS VW450,
FPOIS912.VW455_LAR_EXP_FREQ AS VW455
WHERE VW450.LAR_ID BETWEEN 'LAR1000001' AND 'LAR1000031'
AND VW450.EIND_DT < (CURRENT DATE - VW455.PURGE_FREQ)

could you help me out in this regard.

Rgds
Raghu

umayer · #6 (**permalink**) 01-31-08, 04:47

Quote:

Originally Posted by RaghuING

AND VW450.EIND_DT < (CURRENT DATE - VW455.PURGE_FREQ)

Raghu

AND VW450.EIND_DT < (CURRENT DATE - VW455.PURGE_FREQ MONTH )

you omitted the MONTH keyword ...

jsharon1248 · #7 (**permalink**) 01-31-08, 09:27

Just a cautionary note about using date durations:

Code:

When adding durations to dates, adding one month to a given date
gives the same date one month later unless that date does not exist in the
later month. In that case, the date is set to that of the last day of the later
month. For example, January 28 plus one month gives February 28; and one
month added to January 29, 30, or 31 results in either February 28 or, for a
leap year, February 29.

Note:
If one or more months is added to a given date and then the same number of
months is subtracted from the result, the final date is not necessarily the 
same as the original date.

Internet Services

Web Development

Digital Marketing

Consumer Tech