hi,
i have a char variable with length 6 bytes that contains time in the below format
1014AM
1014PM
..
..
i need to convert into the below foramt
10:14:00 if AM
20:14:00 if PM
Please, let me know if I can use any funtion here. i tried with SUM(INTEGER(SUBSTR(......) but not working.
Thanks
query on sql
Moderators: Kalicharan, Moderator Group
bolastuff1975,
Try this SQL
Results of the two SELECT *
are before update
and after update
Dave
Try this SQL
Code: Select all
DECLARE GLOBAL TEMPORARY TABLE TABLE1
(TIMEIN CHAR(6)
,TIMEOUT CHAR(8)
)
;
INSERT INTO SESSION.TABLE1 (TIMEIN) VALUES('1014AM');
INSERT INTO SESSION.TABLE1 (TIMEIN) VALUES('1014PM');
--;
SELECT *
FROM SESSION.TABLE1
;
UPDATE SESSION.TABLE1 T1
SET TIMEOUT =
CASE
WHEN SUBSTR(T1.TIMEIN,5,2) = 'AM'
THEN SUBSTR(T1.TIMEIN,1,2) || ':' ||
SUBSTR(T1.TIMEIN,3,2) || ':00'
WHEN (SUBSTR(T1.TIMEIN,5,2) = 'PM')
THEN SUBSTR(
DIGITS(CAST(SUBSTR(T1.TIMEIN,1,2) AS DEC(2)) + 12 )
,5,2) || ':' || SUBSTR(T1.TIMEIN,3,2) || ':00'
END
;
--
SELECT *
FROM SESSION.TABLE1
;
are before update
Code: Select all
+-------------------+
| TIMEIN | TIMEOUT |
+-------------------+
1_| 1014AM | ? |
2_| 1014PM | ? |
+-------------------+
Code: Select all
+-------------------+
| TIMEIN | TIMEOUT |
+-------------------+
1_| 1014AM | 10:14:00 |
2_| 1014PM | 22:14:00 |
+-------------------+
-
- Member
- Posts: 8
- Joined: Fri Sep 21, 2007 11:03 pm
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page