MVSFORUMS.com :: View topic - Calculate DB2 date 100 days prior to current date.

Calculate DB2 date 100 days prior to current date.

through

FAQ

[^/[Print]\]
Goto page 1, 2 Next :| |:
MVSFORUMS.com -> Database

#1: Calculate DB2 date 100 days prior to current date. Author: kolusu, Location: San Jose

Posted: Sun Dec 01, 2002 10:58 pm
—

Code:

SELECT DATE(CURRENT DATE - 100 DAYS)
FROM
SYSIBM.SYSDUMMY1
;

you can also use a host vaiable defined as numeric to pass the no: of days to be subtracted.

Code:

01 WS-PRIOR-DATE PIC X(10).
01 WS-DAYS-SUBTRACT PIC S9(04) COMP.

MOVE 100 TO WS-DAYS-SUBTRACT

EXEC SQL
SET :WS-PRIOR-DATE = DATE(CURRENT DATE - :WS-DAYS-SUBTRACT DAYS)

END-EXEC

#2: Author: puneet kumar,

Posted: Sat Dec 07, 2002 6:38 am
—
Hi Kolusu,

My requirement is kind of extension to this topic.
The way we are selecting the date, 100 days prior to the current date. What will be the query if we try to get the max date and min date 'n' months prior to the current date?
Say this month is december and i want to get min date ( 2002-02-01 ) & max date ( 2002-02-28 ) which is 10 months prior to the current date.

Regards,
Puneet

#3: Author: kolusu, Location: San Jose

Posted: Sat Dec 07, 2002 9:32 am
—
Puneet kumar,

The following sql will give you the desired results.

Code:

SELECT DATE(CURRENT DATE - 10 MONTHS - (DAY(CURRENT DATE)) DAYS
+ 1 DAY)
FROM SYSIBM.SYSDUMMY1;

The result of this query is : 2002-02-01

Code:

SELECT DATE(CURRENT DATE - 10 MONTHS - (DAY(CURRENT DATE)) DAYS
+ 1 MONTH)
FROM SYSIBM.SYSDUMMY1;

The result of this query is : 2002-02-28

Hope this helps...

cheers

kolusu

#4: Author: puneet kumar,

Posted: Sat Dec 07, 2002 11:06 am
—
Hi Kolusu,

I really appreciate for the quick response for the queries.
I tested out the queries , but it seems that the first query is working fine where we are fetching minimum date.
But the second query where we are fetching the maximum date is not good for all the cases. Though this query is returning the proper result for '10' months prior date but it is failing when i tried to get the max date for the following cases.
1. March 2. May 3. July 4. October 5. Dec

Please correct me if i am wrong.

Regards,
Puneet

#5: Author: kolusu, Location: San Jose

Posted: Sat Dec 07, 2002 11:19 am
—
Puneet kumar,

Please change the second query for max date to the following.

Code:

SELECT DATE('2002-03-07') - 10 MONTHS - (DAY(CURRENT DATE)) DAYS
+ 1 DAY + 1 MONTH - 1 DAY
FROM SYSIBM.SYSDUMMY1;

The result of this query is:2001-05-31

Code:

SELECT DATE('2002-05-07') - 10 MONTHS - (DAY(CURRENT DATE)) DAYS
+ 1 DAY + 1 MONTH - 1 DAY
FROM SYSIBM.SYSDUMMY1;

The result of this query is:2001-07-31

Code:

SELECT DATE('2002-12-07') - 10 MONTHS - (DAY(CURRENT DATE)) DAYS
+ 1 DAY + 1 MONTH - 1 DAY
FROM SYSIBM.SYSDUMMY1;

The result of this query is:2002-02-28

Hope this helps...

cheers

kolusu

#6: Author: Abhi, Location: India, Pune

Posted: Tue Dec 10, 2002 6:05 am
—
For getting the max date this can also be an option.

Suppose we still go with a date 10 months earlier... then the query is -

Code:

SELECT DATE(CURRENT_DATE - 9 MONTHS -
(DAY(CURRENT_DATE)) DAYS)
FROM SYSIBM.SYSDUMMY1;

#7: Date compare. Author: krk123,

Posted: Fri Nov 19, 2004 10:07 am
—
HI,
I tried searching for posts to find an answer for my question and this is the most relevant one I found. I am trying to do a query on a table who has a DOB of some xxxx-xx-xx date - 1. Can somebody suggest if something like this will work.
select * from table name
WHERE
AND Dmat_TY_C = '01'
AND DMAT-END-D = DATE(ws-birth-date -1 )
Thanks.

#8: Author: kolusu, Location: San Jose

Posted: Fri Nov 19, 2004 10:46 am
—
krk123,

Code:

SELECT *
FROM TABLE
WHERE Dmat_TY_C = '01'
AND DMAT-END-D = DATE(ws-birth-date) - 1 DAY

Check this link which discusses in detail about similar issue.

http://www.mvsforums.com/helpboards/viewtopic.php?t=3087

Hope this helps...

Cheers

Kolusu

#9: Author: krk123,

Posted: Fri Nov 19, 2004 10:57 am
—
oops..I was looking for a date in the subject and thats how I think I didnt go in to this link :SQLCODE -171

Thanks a lot Kolusu. I tried the sql you gave and its giving the exact results I need. Thanks a lot.

#10: Author: whizkid79,

Posted: Fri May 20, 2005 10:21 am
—
I want to find the date difference giving the days months and years as output using a single sql. Is this possible?

Thanks,
Whizkid79

#11: Author: kolusu, Location: San Jose

Posted: Fri May 20, 2005 11:40 am
—
Whizkid79,

An example of sample input and desired output would assist you in getting the right answer

Kolusu

#12: Author: whizkid79,

Posted: Fri May 20, 2005 1:31 pm
—
Hi Kolusu,

I want to find the difference of dates between current date and '05/10/2004' in days months and years in the following format:

<months>/<days>/<years>

i used the following query:

Code:

SELECT
RTRIM(CHAR(MONTH(CURRENT DATE) - MONTH('05/10/2004'))) || '/' ||
RTRIM(CHAR(DAYS(CURRENT DATE) - DAYS('05/10/2004'))) || '/' ||
RTRIM(CHAR(YEAR(CURRENT DATE) - YEAR('05/10/2004')))
FROM SYSIBM.SYSDUMMY1

and i go the result as
0/375/1

Is there any other way to get the difference in all 3 formats?

Thanks,
Whizkid79

#13: Author: kolusu, Location: San Jose

Posted: Fri May 20, 2005 1:52 pm
—
Whizkid79,

The no: of months between 05/10/2004 and current date (05/20/2005) is 12 months. Your query results in zero months which is wrong. check this link which discusses about calucating the difference between 2 dates in terms of months.

http://www.mvsforums.com/helpboards/viewtopic.php?t=3451&highlight=month

The following query will give you the desired results.

Code:

SELECT INT(SUBSTR(DIGITS(
CURRENT DATE - DATE('2004-05-10')),1,4)) * 12 +
INT(SUBSTR(DIGITS(
CURRENT DATE - DATE('2004-05-10')),5,2)) MON_DIFF
,DAYS(CURRENT DATE) - DAYS('2004-05-10') DAYS_DIFF
,YEAR(CURRENT DATE - DATE('2004-05-10')) YEARS_DIFF
FROM SYSIBM.SYSDUMMY1;

The output from this query is

Code:

---------+---------+---------+---------+---------+---------+--------
MON_DIFF DAYS_DIFF YEARS_DIFF
---------+---------+---------+---------+---------+---------+--------
12 375 1

Hope this helps...

Cheers

Kolusu

#14: Author: pcmoonbeam, Location: Orange County, California

Posted: Fri Oct 13, 2006 1:27 pm
—
I'm having some similar problems to those listed above, but they are slightly different in that I'm trying to get the difference in months between to dates in a COBOL program. I'm getting a -171 in the Bind. Here's the offending code:

Code:

EXEC SQL
SELECT INT(SUBSTR(DIGITS(
DATE(:DB2-ARRAY-START-DATE) -
- DATE(:ELGWAIT-WAITPER-DATE)),1,4)) * 12 +
INT(SUBSTR(DIGITS(
DATE(:DB2-ARRAY-START-DATE) -
- DATE(:ELGWAIT-WAITPER-DATE)),5,2)) + 1
INTO :DB2-MONTHS-DIFF
FROM SYSIBM.SYSDUMMY1
END-EXEC.

The host variables named with 'DB2-' high order qualifiers are defined in working storage, the other is part of a DECLGEN. Here's what they look like:

Code:

01 DB2-ARRAY-START-DATE PIC X(10).

Code:

01 DB2-MONTHS-DIFF PIC S9(09) VALUE +0 COMP.

Both the DB2-ARRAY-START-DATE and the ELTWAIT-WAIT-PER-DATE fields are populated in this format 'YYYY-MM-DD'.

This is the error I get at bind time:

I've asked assistance from my co-workers and they haven't been able to decipher this problem so I'm coming to you for help.

Sad

#15: Author: kolusu, Location: San Jose

Posted: Fri Oct 13, 2006 1:52 pm
—

Quote:

DATE(:DB2-ARRAY-START-DATE) -
- DATE(:ELGWAIT-WAITPER-DATE)),1,4)) * 12 +
INT(SUBSTR(DIGITS(
DATE(:DB2-ARRAY-START-DATE) -
- DATE(:ELGWAIT-WAITPER-DATE)),5,2)) + 1

pcmoonbeam,

You have 2 extra MINUS symbols in your query. line 2 and line 5. Remove them and re-compile the program

Hope this helps...

Cheers

Kolusu

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Goto page 1, 2 Next :| |: Page 1 of 2