|
View previous topic :: View next topic
|
| Author |
Message |
rsankar
New User
Joined: 11 Jun 2007
Posts: 2
Location: Chennai
|
|
|
|
Hi,
my requirement is to extract records between the particular month, But it show error - 181, details below
Query:
Select *
from EMP_TABLE
where EMP_DATA BETWEEN :WS-START-DATE AND :WS-END-DATE
EMP_DATA IS DATE Function ( but has only dd,mm,yyyy) no timestamp.
exp: Value like be 2007-06-01
Working storage section :
WS-START-DATE PIC X(10).
WS-END_DATE PIC X(10).
WS-CURRENT_TIMESTAMP PICX(26).
Procedure division:
EXEC SQL
SET :WS-CURRENT-TIMESTAMP = CURRENT TIMESTAMP
END-EXEC
MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-START-DATE(1:8)
MOVE '01' TO WS-START-DATE(9:2)
MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-END-DATE(1:8)
MOVE '31' TO WS-END-DATE(9:2)
I get the current month from the CURRENT Time Stamp. but for Date i mention as 1 to 31 since all the record should be between 1 to 31 for every month, But the problem is for June month it show error -181 since it does not have 31 as date ,
Please advise how can i specify the date for selecting records for month end Jobs, Since the job is schedule as last day of the Month.
Thanks in advance. |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| rsankar wrote: |
Hi,
my requirement is to extract records between the particular month, But it show error - 181, details below
Query:
Select *
from EMP_TABLE
where EMP_DATA BETWEEN :WS-START-DATE AND :WS-END-DATE
EMP_DATA IS DATE Function ( but has only dd,mm,yyyy) no timestamp.
exp: Value like be 2007-06-01
Working storage section :
WS-START-DATE PIC X(10).
WS-END_DATE PIC X(10).
WS-CURRENT_TIMESTAMP PICX(26).
Procedure division:
EXEC SQL
SET :WS-CURRENT-TIMESTAMP = CURRENT TIMESTAMP
END-EXEC
MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-START-DATE(1:8)
MOVE '01' TO WS-START-DATE(9:2)
MOVE WS-CURRENT-TIMESTAMP(1:8) TO WS-END-DATE(1:8)
MOVE '31' TO WS-END-DATE(9:2)
I get the current month from the CURRENT Time Stamp. but for Date i mention as 1 to 31 since all the record should be between 1 to 31 for every month, But the problem is for June month it show error -181 since it does not have 31 as date ,
Please advise how can i specify the date for selecting records for month end Jobs, Since the job is schedule as last day of the Month.
Thanks in advance. |
| Code: |
EXEC SQL
SET :WS-END-DATE = LAST_DAY(DATE(:WS-START-DATE))
END-EXEC
|
|
|
| Back to top |
|
|
rsankar
New User
Joined: 11 Jun 2007
Posts: 2
Location: Chennai
|
|
|
|
One more question is
I m using Between for Date Then why it showing error for 31 in June , Whatever may be date it should fetch the records. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| rsankar wrote: |
One more question is
I m using Between for Date Then why it showing error for 31 in June , Whatever may be date it should fetch the records. |
31 June is not a valid date! When you use dates in SQL they must be valid dates.  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Good catch  |
|
| Back to top |
|
|
|
|
