query on sql

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
bolastuff1975
Member
Posts: 8
Joined: Fri Sep 21, 2007 11:03 pm

query on sql

Post by bolastuff1975 » Mon Sep 24, 2007 7:23 pm

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

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Sat Sep 29, 2007 5:43 am

bolastuff1975,

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          
  ;                              
Results of the two SELECT *

are before update

Code: Select all

  +-------------------+
  | TIMEIN | TIMEOUT  |
  +-------------------+
1_| 1014AM | ?        |
2_| 1014PM | ?        |
  +-------------------+
and after update

Code: Select all

  +-------------------+
  | TIMEIN | TIMEOUT  |
  +-------------------+
1_| 1014AM | 10:14:00 |
2_| 1014PM | 22:14:00 |
  +-------------------+
Dave

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Wed Oct 03, 2007 8:21 pm

bolastuff1975,

Did you have any luck using this SQL?

dave

bolastuff1975
Member
Posts: 8
Joined: Fri Sep 21, 2007 11:03 pm

Post by bolastuff1975 » Thu Oct 04, 2007 1:49 pm

Hi Dave,

Thanks for the SQL.

Yes,it is running correctly on a sample but when i try to run with about 200000 records it bumps up that the datetime value is not correct.

I am not 100% sure of this bit is causing the problem or its due to some other case statements.

Thanks

User avatar
DavidatK
Active Member
Posts: 65
Joined: Tue Mar 27, 2007 8:41 am
Location: Troy, MI USA

Post by DavidatK » Sat Oct 06, 2007 2:22 am

bolastuff1975,

If you don't come to a quick resolution, why not post the entire SQL so we can take a look at it. Give us something to think about :)

Post Reply

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



Other References
Mainframe Tools and others