subtracting 5 days from a date fetched as a result of a db2 select query

DushyantG · #1 (**permalink**) 04-15-11, 02:34

Hi,
I want to subtract say 5 days from the date obtained as the result of a db2 sql query.

I was trying this

Code:

SELECT  ((SELECT ENROLL_DATE FROM RECORDS.ENROLL_RECORDS WHERE FEE_RECEIPT_NO=5002) -5 DAYS ) FROM SYSIBM.SYSDUMMY1

but it doesn't work.

WHILE this works:

Code:

SELECT  (DATE('1997-04-03')-5 DAYS) from sysibm.sysdummy1

When I just execute

Code:

SELECT ENROLL_DATE FROM RECORDS.ENROLL_RECORDS WHERE FEE_RECEIPT_NO=5002

it gives me 02/15/2010

Plz help.

stolze · #2 (**permalink**) 04-15-11, 05:07

Does the last query happen to return more than 1 row? If so, you would try to subtract 5 days from a set of rows. That doesn't work, of course, because a set of rows is not a DATE value.

Try this instead:

Code:

SELECT ENROLL_DATE - 5 DAYS
FROM    RECORDS.ENROLL_RECORDS
WHERE   FEE_RECEIPT_NO=5002

DushyantG · #3 (**permalink**) 04-15-11, 05:25

Thanks for the prompt reply.

No, that query only returns 1 single value (1 row, 1 column).
And I forgot to say that ENROLL_DATE is not a db2 date but a varchar type.
So I can't try this

Code:

SELECT ENROLL_DATE - 5 DAYS
FROM    RECORDS.ENROLL_RECORDS
WHERE   FEE_RECEIPT_NO=5002

But I am sure that the string that

Code:

SELECT ENROLL_DATE - 5 DAYS
FROM    RECORDS.ENROLL_RECORDS
WHERE   FEE_RECEIPT_NO=5002

returns is in db2 date format. It's just not stored as a db2 date in the table its retrieved from.

Now can u guide me plz. ?

Stealth_DBA · #4 (**permalink**) 04-15-11, 06:34

First, if you have a Date value, it should be stored in a DATE data type column. Otherwise you are wasting space (4 bytes compared to 8 or 10 bytes (depending on if you store the separator)).

Second, if you have to store it as text, it should be CHAR and not VARCHAR as it will use less space. Since all dates would be 8 or 10 characters, the VARCHAR doesn't get you anything and wastes 2 bytes.

If you store the value in a valid date format, this should get you what you want:

Code:

SELECT DATE(ENROLL_DATE) - 5 DAYS
FROM    RECORDS.ENROLL_RECORDS
WHERE   FEE_RECEIPT_NO=5002

You could also use CAST:

Code:

SELECT CAST(ENROLL_DATE AS DATE) - 5 DAYS
FROM    RECORDS.ENROLL_RECORDS
WHERE   FEE_RECEIPT_NO=5002

stolze · #5 (**permalink**) 04-15-11, 10:18

Quote:

Originally Posted by DushyantG

And I forgot to say that ENROLL_DATE is not a db2 date but a varchar type.

So you have a string and not a date. The string value is something you want to have interpreted as date. SQL (in general) has strong typing. Thus, you have to apply some type conversion before.

Internet Services

Web Development

Digital Marketing

Consumer Tech