| View previous topic :: View next topic |
| Author |
Message |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
 Posted: Sun Dec 01, 2002 10:58 pm Post subject: Calculate DB2 date 100 days prior to current date. |
 |
|
| 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
|
|
|
| Back to top |
|
 |
puneet kumar
Beginner
Joined: 07 Dec 2002
Posts: 5
Topics: 2
|
| Posted: Sat Dec 07, 2002 6:38 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Sat Dec 07, 2002 9:32 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
puneet kumar
Beginner
Joined: 07 Dec 2002
Posts: 5
Topics: 2
|
| Posted: Sat Dec 07, 2002 11:06 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Sat Dec 07, 2002 11:19 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
Abhi
Beginner
Joined: 03 Dec 2002
Posts: 21
Topics: 4
Location: India, Pune
|
| Posted: Tue Dec 10, 2002 6:05 am Post subject: |
|
|
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;
|
|
|
| Back to top |
|
|
krk123
Beginner
Joined: 03 Jun 2003
Posts: 58
Topics: 19
|
| Posted: Fri Nov 19, 2004 10:07 am Post subject: Date compare. |
|
|
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. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
krk123
Beginner
Joined: 03 Jun 2003
Posts: 58
Topics: 19
|
| Posted: Fri Nov 19, 2004 10:57 am Post subject: |
|
|
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. |
|
| Back to top |
|
|
whizkid79
Beginner
Joined: 29 Sep 2004
Posts: 53
Topics: 14
|
| Posted: Fri May 20, 2005 10:21 am Post subject: |
|
|
I want to find the date difference giving the days months and years as output using a single sql. Is this possible?
Thanks,
Whizkid79 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Fri May 20, 2005 11:40 am Post subject: |
|
|
Whizkid79,
An example of sample input and desired output would assist you in getting the right answer
Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
whizkid79
Beginner
Joined: 29 Sep 2004
Posts: 53
Topics: 14
|
| Posted: Fri May 20, 2005 1:31 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Fri May 20, 2005 1:52 pm Post subject: |
|
|
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
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
pcmoonbeam
Beginner
Joined: 21 Nov 2005
Posts: 31
Topics: 10
Location: Orange County, California
|
| Posted: Fri Oct 13, 2006 1:27 pm Post subject: |
|
|
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:
DSNX200I -DB2T BIND SQL ERROR
USING JMOON AUTHORITY
PLAN=(NOT APPLICABLE)
DBRM=RHRP121
STATEMENT=822
SQLCODE=-171
SQLSTATE=42815
TOKENS=1 DSNMINUS
CSECT NAME=DSNXOBFA
RDS CODE=30
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.
_________________
Thanks,
PCMOONBEAM |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Fri Oct 13, 2006 1:52 pm Post subject: |
|
|
| 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
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
