sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Some SQL stuff that everyone already knows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Some SQL stuff that everyone already knows

    Code:
         H dftactgrp( *no )  OPTION(*NODEBUGIO)                                                         
                                                                                                        
           //---------------------------------------------                                              
           //  using SQL date and time functions                                                        
           // IBM DB2 *ISO *USA *JIS *EUR date formats                                                  
           // SQL values and set options                                                                
           //---------------------------------------------                                              
                                                                                                        
         d Mystring        s            512    varying                                                  
         d MyStamp         s               z                                                            
         d MyIsoDate       s               d                                                            
         d SQLIso          s             10                                                             
         d SQLUsa          s             10                                                             
         d SQLEur          s             10                                                             
         d SQLJis          s             10                                                             
         d SQLUsaDate      s               d   datfmt(*USA)                                             
         d WorkIsoDate     s               d                                                            
                                                                                                        
          /Free                                                                                         
                                                                                                        
                  exec sql set option commit=*none,datfmt=*iso;                                         
                                                                                                        
                 //---------------------------------------------                                        
                 // M A I N  L I N E                                                                    
                 //---------------------------------------------                                        
                                                                                                        
                 MyString = 'This is a upper case test';                                                
                                                                                                        
                 exec sql                                                                               
                  select upper(:MyString)                                                               
                  into :MyString                                                                        
                      from sysibm/sysdummy1;                                                            
                                                                                                        
                  // after processed string will be:                                                    
                  //  'THIS IS A UPPER CASE TEST'                                                       
                                                                                                        
                  // retrieve a timestamp in SQL                                                        
                 exec sql                                                                               
                  select (CURRENT TIMESTAMP)                                                            
                  into :MyStamp                                                                         
                  from sysibm/sysdummy1;                                                                
                                                                                                        
                  // after processed MyStamp will be something like:                                    
                  // '2012-01-04-08.05.08.811000'                                                       
                                                                                                        
                                                                                                        
                  // retrieve a date in 'YYYY /MM / DD' format                                          
                 exec sql                                                                               
                  select (CURRENT_DATE)                                                                 
                  into :MyIsoDate                                                                       
                  from sysibm/sysdummy1;                                                                
                                                                                                        
                  // some SQL data math                                                                 
                 exec sql                                                                               
                  select (CURRENT_DATE + 3 YEARS + 2 MONTHS + 15 DAYS)                                  
                  into :MyIsoDate                                                                       
                  from sysibm/sysdummy1;                                                                
                                                                                                        
                  // select date in character multiple formats                                          
                  exec sql                                                                              
                  select  char(CURRENT_DATE,ISO),                                                       
                          char(CURRENT_DATE,USA),                                                       
                          char(CURRENT_DATE,EUR),                                                       
                          char(CURRENT_DATE,JIS)                                                        
                  into :SQLiso, :SQLusa, :SQLeur, :SQLjis                                               
                  from sysibm/sysdummy1;                                                                
                                                                                                        
                  // select date in usa format *note  datfmt on variable definition                     
                  exec sql                                                                              
                  select  (CURRENT_DATE)                                                                
                  into :SQLUSADate                                                                      
                  from sysibm/sysdummy1;                                                                
                                                                                                        
                  //output : '01/04/2012'                                                               
                                                                                                        
                  // now that I've posted this all in selects using sysdummy try using                  
                  // Values or set                                                                      
                                                                                                        
                  exec sql values(CURRENT_DATE - 30 DAYS) into :WorkIsoDate;                            
                  exec sql Set :WorkIsoDate = Current_Date - 30 Days;                                   
                                                                                                        
                  *inlr = *On;                                                                          
                                                                                                        
          /End-Free
    Attached Files
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

sponsored links

Collapse

Working...
X