Convert serial number date to format mm/dd/yyyy in DB2 1

[ceddins]

Good afternoon,

I'm extremely new to DB2. I don't know how to convert a serial date to a common date format. The database I am trying to pull data from uses a serial date which represents the number of days from 01/01/1900. So, June 1 2011 is stored as 40694 in the database. Is there a way to convert the serial date to a mm/dd/yyyy format?

Thank you in advance for your help.

-Clint

 

[MarcLodge]

Hi ceddins,
DB2's starting date is 0001-01-01. The DAYS function gives you the number of days since (and including) that date.
Your date is stored as the number of days since 1900-01-01, so all you need to do is add the number of days between 0001-01-01 and 1900-01-01 and convert it into a date format.

Normally here at tek tips, we would leave it at that and let you work out the SQL yourself as there is a great satisfaction in working these things out if given a hint.As you are extremely new to DB2, you might appreciate the code as it is little complex. Please do not feel I am patronising you, and accept my apologies if you feel that way - only trying to help.

What I think you need is:
SELECT DATE(DAYS('1900-01-01') - 1 + yourdatecolumninhere)
FROM SYSIBM.SYSDUMMY1

Hope this helps
Marc

 

[tcsbiz]

Clint,

Which DB2 are you using? What you are describing is what was used on the AS400/iSeries/i5/i pre-Y2K. It was a way to get around the limitations of dates on the machine. That was taken care of when the machine got a date data type.

We call it a hundred-year-format (HYF) date. We are still running code that uses it. The code was originally written in the late-'80's, early '90's time frame. I have RPG code that converts the date from HYF to Gregorian to Julian. I don't know if it's the same thing as what you're dealing with.

I'll check with my boss to see if I can share the code. The code is either in old-style RPGII or RPGIII.

 

[ceddins]

MarcLodge:

Thanks so much for your suggestion, but I was unable to get it to work. I don't know where to write the "FROM SYSIBM.SYSDUMMY1." I basically just started using this program last week, so any help you give me is appreciated. Also, I think the serial date is being stored as a decimal, I'm not sure if that is useful information or not.

tcsbiz:

Thank you for your reply. I believe you are correct that I am using AS400/iSeries/i5/i pre-Y2K. Please do check with your boss whether you can share or not. I would love to be able to find a solution to this!

 

[MarcLodge]

Hi Ceddins,
SYSIBM.SYSDUMMY1 is an IBM table that exists in every IBM DB2 system and was used here just as an example.

In order to get it to work properly try replacing SYSIBM.SYSDUMMY1 with your tablename e.g.

SELECT DATE(DAYS('1900-01-01') - 1 + yourdatecolumninhere)
FROM yourtablenamehere

I believe this will work.

Marc

 

[ceddins]

Marc,

This worked wonderfully. Thank you so much.

Now, I have the data pull set to where the user is prompted to enter the serial number date in order for the data pull to run. Is there a way I can write the parameters so that they can enter the date in MM/DD/YYYY format instead? I assume this would go into the WHERE section...

Here's what I have so far:

JOIN BY T1.KYDTGI = T2.KYDTGS AND T1.SEQGI = T2.SEQGS

SELECT (DATE(DAYS('1900-01-01') - 1 + T1.KYDTGI)) AS Key_Date,(T1.RSAGGI) AS Booking_Agent,(T1.LNAMGI) AS Gst_Lst_Nm,(T1.FNAMGI) AS Gst_Fst_Nm,(DATE(DAYS('1900-01-01') - 1 + T2.RCDTGS)) AS Upsell_Date,(T2.OPIDGS) AS Upsell_Agent,(T2.RQCDGS) AS Upsell_Code,(T2.REQGS) AS Upsell_Cmnts,(T1.ACTPGI) AS Rm_Type,(T1.RMNOGI) AS Rm_No

WHERE (T2.STATGS <> 'D') AND (SUBSTR(T2.REQGS,1,4) = 'RUID') AND (T1.KYDTGI BETWEEN ?/*LABEL Enter Beginning Key Date ENDLABEL*/ AND ?/*LABEL Enter Ending Key Date ENDLABEL*/)

 

[MarcLodge]

Hi ceddins,
What you need to do now is convert what you already have got back the other way, so, if you want to compare the serial date on the table with a DB2 date, then you'll need to specify the WHERE clause something like:

AND ( DATE(DAYS('1900-01-01' -1 + T1.KYDTG1)) BETWEEN enteredbegindate AND enteredenddate )

Be aware of what your local standard is for a date. You may have to use USA, ISO etc. in order to obtain the correct match.

Hope this helps.
Marc

 

[tcsbiz]

Here is the code that we are using. It's marked as being from 1989.

C           *ENTRY    PLIST                   
C                     PARM           CIN     8
C                     PARM           HIN     8
C                     PARM           FORMAT  4
C                     PARM           #PERR   1
C                     PARM           #PDAY   4
C*
C                     MOVE CIN       #PCIN                          
C                     MOVE HIN       #PHIN                          
C*                                                                  
C                     MOVE *ZEROS    #PERR            CLR ERROR FLAG
C                     Z-ADD#PHIN     #PHDTE                         
C           #PCIN     IFGT *ZEROS                                   
C           #PHIN     CABEQ*ZEROS    #PTAG1           CALENDAR INPUT
C                     END                                           
C           #PHIN     IFGT *ZEROS                                   
C           #PCIN     CABEQ*ZEROS    #PTAG2           100 YEAR INPUT
C                     END                                           
C                     GOTO #PTAG3                                   
C           #PTAG1    TAG                                           
C                     Z-ADD#PCIN     #PCYY   30                     
C**                   Z-ADD#PCYY     #PCYR                          
C           FORMAT    IFEQ 'MDCY'                     MMDDYY INPUT  
C                     MOVEL#PCIN     #PWRK4  40                     
C                     MOVEL#PWRK4    #PCMON                         
C                     MOVE #PWRK4    #PCDAY                         
C                     MOVE #PCIN     #PCYR                          
C                     ELSE                                        
C           FORMAT    IFEQ 'DMCY'                     DDMMYY INPUT
C                     MOVEL#PCIN     #PWRK4  40                   
C                     MOVEL#PWRK4    #PCDAY                       
C                     MOVE #PWRK4    #PCMON                       
C                     MOVE #PCIN     #PCYR                        
C                     ELSE                                        
C           FORMAT    IFEQ 'CYMD'                     YYMMDD INPUT
C                     MOVE #PCIN     #PWRK4  40                   
C                     MOVEL#PCIN     #PCYR                        
C                     MOVEL#PWRK4    #PCMON                       
C                     Z-ADD#PCIN     #PCDAY                       
C                     END                                         
C                     END                                         
C                     END                                         
C                     Z-ADD#PCYR     #PWRK4                       
C*                                                                
C                     SUB  1900      #PWRK4                       
C*                                                                
C           #PCMON    IFLT 3                                      
C                     SUB  1         #PWRK4                          
C           #PCMON    ADD  13        #PWRK3  30                      
C                     ELSE                                           
C           #PCMON    ADD  1         #PWRK3                          
C                     END                                            
C           #PWRK4    MULT 365.25    #PHDTE                          
C                     MULT 30.61     #PWRK3                          
C                     ADD  #PWRK3    #PHDTE                          
C                     ADD  #PCDAY    #PHDTE                          
C                     SUB  63        #PHDTE                          
C           #PTAG2    TAG                                            
C           #PHDTE    CABLT25568     #PTAG3           B4  JAN.01,1970
C           #PHDTE    CABGT99999     #PTAG3           AFT A LONG TIME
C           #PHDTE    DIV  365.25    #PHIN                           
C                     Z-ADD#PHIN     #PCYY                           
C           #PCYY     ADD  1900      #PCYR                           
C                     MULT 365.25    #PHIN                           
C           #PHDTE    SUB  #PHIN     #PWRK3                          
C           #PCYY     DIV  4         #PHIN                           
C                     MVR            #PWRK1  10
C           #PWRK3    IFLE 59                  
C                     ADD  365       #PWRK3    
C           #PWRK1    IFEQ *ZEROS              
C                     ADD  1         #PWRK3    
C                     END                      
C                     END                      
C                     ADD  63        #PWRK3    
C           #PWRK3    DIV  30.61     #PCMON    
C           #PCMON    MULT 30.61     #PWRK4    
C           #PWRK3    SUB  #PWRK4    #PCDAY    
C           #PCMON    IFGT 13                  
C                     SUB  12        #PCMON    
C                     END                      
C                     SUB  1         #PCMON    
C****                 Z-ADD#PCYR     #PCDTE    
C           FORMAT    IFEQ 'MDCY'              
C                     MOVE #PCDAY    #PWRK4    
C                     MOVEL#PCMON    #PWRK4    
C                     MOVEL#PWRK4    #PCDTE    
C                     MOVE #PCYR     #PCDTE                        
C                     ELSE                                         
C           FORMAT    IFEQ 'DMCY'                                  
C                     MOVE #PCMON    #PWRK4                        
C                     MOVEL#PCDAY    #PWRK4                        
C                     MOVEL#PWRK4    #PCDTE                        
C                     MOVE #PCYR     #PCDTE                        
C                     ELSE                                         
C           FORMAT    IFEQ 'CYMD'                                  
C                     MOVEL#PCYR     #PCDTE                        
C                     MOVEL#PCMON    #PWRK4                        
C                     MOVE #PCDAY    #PWRK4                        
C                     MOVE #PWRK4    #PCDTE                        
C                     END                                          
C                     END                                          
C                     END                                          
C           #PCIN     IFGT *ZEROS                                  
C           #PCIN     CABNE#PCDTE    #PTAG3           BUM CAL. DATE
C                     END                                          
C           #PHDTE    DIV  7         #PHIN                         
C                     MVR            #PWRK1
C           #PWRK1    IFEQ *ZERO           
C                     MOVE 'SUN.'    #PDAY 
C                     ELSE                 
C           #PWRK1    IFEQ 1               
C                     MOVE 'MON.'    #PDAY 
C                     ELSE                 
C           #PWRK1    IFEQ 2               
C                     MOVE 'TUE.'    #PDAY 
C                     ELSE                 
C           #PWRK1    IFEQ 3               
C                     MOVE 'WED.'    #PDAY 
C                     ELSE                 
C           #PWRK1    IFEQ 4               
C                     MOVE 'THU.'    #PDAY 
C                     ELSE                 
C           #PWRK1    IFEQ 5               
C                     MOVE 'FRI.'    #PDAY 
C                     ELSE                 
C           #PWRK1    IFEQ 6               
C                     MOVE 'SAT.'    #PDAY                  
C                     END                                   
C                     END                                   
C                     END                                   
C                     END                                   
C                     END                                   
C                     END                                   
C                     END                                   
C                     GOTO #PTAG4                           
C           #PTAG3    TAG                             ERRORS
C                     Z-ADD*ZEROS    #PCDTE  80             
C                     Z-ADD*ZEROS    #PHDTE  80             
C                     Z-ADD*ZEROS    #PCMON  20             
C                     Z-ADD*ZEROS    #PCDAY  20             
C                     Z-ADD*ZEROS    #PCYR   40             
C                     MOVE *BLANKS   #PDAY   4              
C                     MOVE '1'       #PERR   1              
C           #PTAG4    TAG                                   
C                     Z-ADD*ZEROS    #PHIN   80             
C                     Z-ADD*ZEROS    #PCIN   80             
C*                                          
C                     MOVE #PCDTE    CIN    
C                     MOVE #PHDTE    HIN    
C*                                          
C                     MOVE '1'       *INLR

 

[ceddins]

tcsbiz,

thanks for taking the time and going through the trouble of posting that code for me. Unfortunately, it is truly Greek to me. I am not exactly sure what any of it means. I apologize. Luckily, Marc's solution did work for me.

Thank you both so much for your help!

-Clint