× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have a recurring issue with SQL statements I create here.  I am repeatedly
comparing date values that are stored as numeric fields in MMDDYY format.
The dates are stored in 6,0 and 7.0 fields depending on the data file.  I
have managed to come up with a solution but it's pretty complex (ugly?) and
I am wondering if there isn't a better way to do this.  I'm attaching a
recent statement as an example of what I'm talking about.  Notice all the
repetitive code.  Is there a way to build a field once and just reference it
from then on?  Is there a better way to convert numeric values to date
fields?

LHPOST = 7,0  MMDDYY
LHEFF = 7,0  MMDDYY
LNNXMT = 6,0  MMDDYY

select lnnote, lnstat, lhtc1,                                     
  case char_length(Trim(b from char(LHPOST)))                     
    When 5 then case                                              
                  When substr(char(LHPOST), 4, 2) >= '40' then    
                    '19' || substr(char(LHPOST), 4, 2) || '-' ||  
                     '0' || substr(char(LHPOST), 1, 1) || '-' ||  
                     substr(char(LHPOST), 2, 2)                   
                                                                  
                  When substr(char(LHPOST), 4, 2) < '40' then     
                    '20' || substr(char(LHPOST), 4, 2) || '-' ||  
                     '0' || substr(char(LHPOST), 1, 1) || '-' ||  
                     substr(char(LHPOST), 2, 2)                   
                End                                               
                                                                  
    When 6 then case                                              
                  When substr(char(LHPOST), 5, 2) >= '40' then    
                    '19' || substr(char(LHPOST), 5, 2) || '-' ||  
                     substr(char(LHPOST), 1, 2) || '-' ||        
                     substr(char(LHPOST), 3, 2)                  
                                                                 
                  When substr(char(LHPOST), 5, 2) < '40' then    
                    '20' || substr(char(LHPOST), 5, 2) || '-' || 
                     substr(char(LHPOST), 1, 2) || '-' ||        
                     substr(char(LHPOST), 3, 2)                  
                End                                              
  End as Post_Date,                                              
                                                                 
lhamt1, lhrecn,                                                  
  case char_length(Trim(b from char(LHEFF)))                     
    When 5 then case                                             
                  When substr(char(LHEFF), 4, 2) >= '40' then    
                    '19' || substr(char(LHEFF), 4, 2) || '-' ||  
                     '0' || substr(char(LHEFF), 1, 1) || '-' ||  
                     substr(char(LHEFF), 2, 2)                   
                                                                 
                  When substr(char(LHEFF), 4, 2) < '40' then    
                    '20' || substr(char(LHEFF), 4, 2) || '-' || 
                     '0' || substr(char(LHEFF), 1, 1) || '-' || 
                     substr(char(LHEFF), 2, 2)                  
                End                                             
                                                                
    When 6 then case                                            
                  When substr(char(LHEFF), 5, 2) >= '40' then   
                    '19' || substr(char(LHEFF), 5, 2) || '-' || 
                     substr(char(LHEFF), 1, 2) || '-' ||        
                     substr(char(LHEFF), 3, 2)                  
                                                                
                  When substr(char(LHEFF), 5, 2) < '40' then    
                    '20' || substr(char(LHEFF), 5, 2) || '-' || 
                     substr(char(LHEFF), 1, 2) || '-' ||        
                     substr(char(LHEFF), 3, 2)                  
                End                                             
  End as Eff_Date,                                              
                                                                
  case char_length(Trim(b from char(lnnxmt)))                   
    When 5 then case                                            
                  When substr(char(lnnxmt), 4, 2) >= '40' then  
                    '19' || substr(char(lnnxmt), 4, 2) || '-' ||
                     '0' || substr(char(lnnxmt), 1, 1) || '-' ||
                     substr(char(lnnxmt), 2, 2)                 
                                                                
                  When substr(char(lnnxmt), 4, 2) < '40' then   
                    '20' || substr(char(lnnxmt), 4, 2) || '-' ||
                     '0' || substr(char(lnnxmt), 1, 1) || '-' ||
                     substr(char(lnnxmt), 2, 2)                 
                End                                             
                                                                
    When 6 then case                                            
                  When substr(char(lnnxmt), 5, 2) >= '40' then  
                    '19' || substr(char(lnnxmt), 5, 2) || '-' ||
                     substr(char(lnnxmt), 1, 2) || '-' ||       
                     substr(char(lnnxmt), 3, 2)                   
                                                                  
                  When substr(char(lnnxmt), 5, 2) < '40' then     
                    '20' || substr(char(lnnxmt), 5, 2) || '-' ||  
                     substr(char(lnnxmt), 1, 2) || '-' ||         
                     substr(char(lnnxmt), 3, 2)                   
                End                                               
  End as Nxt_Mat_Dte                                              
                                                                  
from lnp00701 join lnp00301 on lhtc1 = 85 and lhnote = lnnote and 
lnstat = ' '                                                      
                                                                  
where                                                             
  case char_length(Trim(b from char(LHEFF)))                      
    When 5 then case                                              
                  When substr(char(LHEFF), 4, 2) >= '40' then     
                    '19' || substr(char(LHEFF), 4, 2) || '-' ||   
                     '0' || substr(char(LHEFF), 1, 1) || '-' ||   
                     substr(char(LHEFF), 2, 2)                  
                                                                
                  When substr(char(LHEFF), 4, 2) < '40' then    
                    '20' || substr(char(LHEFF), 4, 2) || '-' || 
                     '0' || substr(char(LHEFF), 1, 1) || '-' || 
                     substr(char(LHEFF), 2, 2)                  
                End                                             
                                                                
    When 6 then case                                            
                  When substr(char(LHEFF), 5, 2) >= '40' then   
                    '19' || substr(char(LHEFF), 5, 2) || '-' || 
                     substr(char(LHEFF), 1, 2) || '-' ||        
                     substr(char(LHEFF), 3, 2)                  
                                                                
                  When substr(char(LHEFF), 5, 2) < '40' then    
                    '20' || substr(char(LHEFF), 5, 2) || '-' || 
                     substr(char(LHEFF), 1, 2) || '-' ||        
                     substr(char(LHEFF), 3, 2)                  
                End                                             
  End                                                           
>                                                               
  case char_length(Trim(b from char(lnnxmt)))                   
    When 5 then case                                            
                  When substr(char(lnnxmt), 4, 2) >= '40' then  
                    '19' || substr(char(lnnxmt), 4, 2) || '-' ||
                     '0' || substr(char(lnnxmt), 1, 1) || '-' ||
                     substr(char(lnnxmt), 2, 2)                 
                                                                
                  When substr(char(lnnxmt), 4, 2) < '40' then   
                    '20' || substr(char(lnnxmt), 4, 2) || '-' ||
                     '0' || substr(char(lnnxmt), 1, 1) || '-' ||
                     substr(char(lnnxmt), 2, 2)                 
                End                                             
                                                                
    When 6 then case                                            
                  When substr(char(lnnxmt), 5, 2) >= '40' then  
                    '20' || substr(char(lnnxmt), 4, 2) || '-' ||
                     '0' || substr(char(lnnxmt), 1, 1) || '-' ||
                     substr(char(lnnxmt), 2, 2)                 
                End                                             
                                                                
    When 6 then case                                            
                  When substr(char(lnnxmt), 5, 2) >= '40' then  
                    '19' || substr(char(lnnxmt), 5, 2) || '-' ||
                     substr(char(lnnxmt), 1, 2) || '-' ||       
                     substr(char(lnnxmt), 3, 2)                 
                                                                
                  When substr(char(lnnxmt), 5, 2) < '40' then   
                    '20' || substr(char(lnnxmt), 5, 2) || '-' ||
                     substr(char(lnnxmt), 1, 2) || '-' ||       
                     substr(char(lnnxmt), 3, 2)                 
                End                                             
  End                                                           


Rick

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.