View previous topic :: View next topic
|
Author |
Message |
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
I tried below snippet to get the time difference in minutes from timestamp.
05 WS-TIME-DIFF PIC X(04).
05 BDR-BKT-REQ-TIMESTMP PIC X(26).
EXEC SQL
SET :WS-TIME-DIFF = MINUTE(CURRENT TIME)
- MINUTE(:BDR-BKT-REQ-TIMESTMP)
END-EXEC
it is getting compiled successfully,but giving follwowing bind error
DSNX200I -FDSN BIND SQL ERROR
SQLCODE=-408
SQLSTATE=42821
TOKENS=*N
CSECT NAME=DSNXOCAS
RDS CODE=-100
(sqlcode =-408 description - The data type that is to be assigned is incompatible with the declared data
type of the assignment target. )
logically differnce will be of numeric ,but if i change WS-TIME-DIFF datatype to numeric ,it is giving compilation error.
so how can we handle this scenario to get the time diff,,,pls tell me if anyone know alternate way to get time diff in minutes. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
suggest you look at a db2 manual,
time arithmetic returns a DECIMAL(6,0) data type (which is comp-3 cobol data type) |
|
Back to top |
|
|
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
Thanks |
|
Back to top |
|
|
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
NOW I CHANGED THE VARIABLE DECLARATION TO
05 WS-TIME-DIFF PIC S9(6)V USAGE COMP-3.
BUT WHILE IM DEBUGGING I FOUND SQLCODE -180.
(sqlcode =-180 description The length or string representation of a DATE, TIME, or TIMESTAMP value does
ot conform to any valid format.
the value can contain one of the following:
For a host variable, the position number of the input host variable. If
the position number cannot be determined, a blank is displayed.
For a character string constant, the character string constant. The
maximum length that is displayed is the length of SQLERRM.
For a character column, the column name. If the column is a VIEW column
and it has a corresponding base column, the VIEW column name is
displayed. If the column is a VIEW column but it does not have a
corresponding base column, a string of '*N' is displayed. )
SO HOW DO I CONTINUE NOW? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Maybe :BDR-BKT-REQ-TIMESTMP is not properly populated. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
The first error you had was a syntax error.
the current error you are encountering is a run-time error. |
|
Back to top |
|
|
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
THAT I AM POPULATING WHILE DEBUGGING
SAMPLE DATA IS '2011-06-15-02.01.56.178837' |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
ok, db2 is wrong, has a bug, call IBM |
|
Back to top |
|
|
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
oh dbz i dint expect this from you....could you tell me any alternate way to get that time ,if any... |
|
Back to top |
|
|
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
i found this but useless
SELECT TIMESTAMPDIFF (4, CHAR(
TIMESTAMP('2011-06-14-11.58.00')-
TIMESTAMP('2011-06-14-11.55.00'))) FROM SYSIBM.SYSDUMMY1
i cant use SYSIBM.SYSDUMMY1 in online program(i.e., within EXEC CICS... END-EXEC) |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
Just wondering, do you think you are getting a timestamp as a number of minutes, or do you think you are getting the minutes part of the timestamp? |
|
Back to top |
|
|
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
want to get the minutes part of the timestamp...
do u have any idea? |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
i cant use SYSIBM.SYSDUMMY1 in online program(i.e., within EXEC CICS... END-EXEC) |
1. the comment about sysdummy1 is false.
2. no wonder you are having problems with your sql. you need to enclose your sql with EXEC SQL END-EXEC
ok, I have compiled the following in both COBOL 2 and Enterprise-COBOL.
Cobol2 is db2 vsn 7
e-cobol is db2 vsn 9
working-storage:
Code: |
03 WS-TIME-DIFF PIC S9(6) COMP-3.
03 WS-TIMESTAMP PIC X(26) VALUE
'2011-06-15-02.01.56.178837'.
|
procedure division:
Code: |
EXEC SQL
SET :WS-TIME-DIFF = MINUTE(CURRENT TIME)
- MINUTE(TIMESTAMP(:WS-TIMESTAMP))
END-EXEC
DISPLAY WS-TIME-DIFF
EXEC SQL
SET :WS-TIME-DIFF = MINUTE(CURRENT TIME)
- MINUTE(:WS-TIMESTAMP)
END-EXEC
DISPLAY WS-TIME-DIFF
|
both sqls for both program provided the same result.
i created the first with the double CAST - min(timestamp())
in case db2 was having trouble deciding exactly what was in the host variable.
but that was not the case for either db2 version.
so back to the -180.
it is either due to CURRENT_TIME
or
your host variable.
you choose.
SO,
you are either not truthful - with your code
- the contents of your host variable
either way,
the above is tested code.
suggest you RTFM and be more exact with
your code
and your debugging
than you have been posting in this thread. |
|
Back to top |
|
|
Arun bv
New User
Joined: 29 Dec 2010 Posts: 41 Location: Mumbai
|
|
|
|
Thanks for trying out in MF
about the comment EXEC CICS ,,,,,i knew it is EXEC SQL ,,here its a typo error ,,,
and abt the CURRENT TIME, below sql query works
SELECT MINUTE(CURRENT TIME)
FROM SYSIBM.SYSDUMMY1
another working query
05 WS-TEMP-DATE PIC X(10).
EXEC SQL
SET :WS-TEMP-DATE = DATE(CURRENT DATE)
- 6 MONTHS
END-EXEC
if so why not in the above mentioned query(i.e the query which i posted here to get the time diff). |
|
Back to top |
|
|
|