Omit Commas from Numeric Dates

Are you tired of seeing commas (or whatever thousands separator you use) in dates when querying data with SQL? Are your users underwhelmed when you give them a quick-and-dirty report that has dates like 20,060,301 and 20,051,124 on it? Do you wish SQL had some way you could easily reformat a date to get rid of commas and maybe even insert some more traditional separator characters?

The only SQL implementations I know of that allow you to format or edit a number are Sequel, from Advanced Systems Concepts, and Microsoft Access. If you’re using most any other version of SQL, such as SQL/400, you’re out of luck. Well, not really. Here are some things you can do to make your data more presentable.

Modify the DDS: The reason the commas are in the date is that whoever wrote the DDS for the file didn’t put an editing keyword on the numeric field. Add the appropriate edit word to the DDS.

A            TDATE          8  0       TEXT('TRANSACTION DATE')
A                                      EDTWRD('    -  -  ')
A* notice the blanks in the edit word          ^^^^ ^^ ^^

Use CHGPF to revise the definition.

CHGPF FILE(MYLIB/MYFILE) SRCFILE(MYLIB/QDDSSRC)

You will not have to recompile the programs that use the file because changing the editing will not give you a level check.

Use the SQL data conversion functions: Here’s an example that uses built-in SQL data-conversion functions to reformat an eight-digit date in ISO format to the MM/DD/YY format typically used in the United States of America.

select SomeDate, 
  substr(char(SomeDate),5,2)||'/'||   
  substr(char(SomeDate),7,2)||'/'||   
  substr(char(SomeDate),3,2)          
from $$data/cohead

That’s too messy for my taste. There’s too much substringing and concatenating. Char converts the numeric value to a character value. I could have also used DIGITS and probably VARCHAR. SUBSTR picks out certain parts of the date–month, day, year. In spite of my aversion to it, I’m sure I haven’t used that technique for the last time. Here’s the output from the query.

SOMEDATE    String Expression
20,051,221     12/21/05    
20,051,215     12/15/05    
20,051,223     12/23/05    
20,051,222     12/22/05    
20,060,112     01/12/06    
20,051,214     12/14/05    
20,060,130     01/30/06

Write your own reformatting function: I got this idea from a technique I learned from Matt Sargent many, many years ago. He told me that the RPG XLATE op code provides an easy way to reformat character strings. The idea is to translate a pattern to the characters in the original date string. For example, translate the characters ABCDEFGH to 20060301 in the following string: EF/GH/CD. The result is a string with the value 03/01/06.

D  FrPat          s              8a                
D  FrDate         s              8a                
D  ToPat          s              8a                
D  ToDate         s              8a                
                                                   
C                   eval      FrPat  = 'ABCDEFGH'  
C                   eval      FrDate = '20060329'  
C                   eval      ToPat  = 'EF/GH/CD'  
C     FrPat:FrDate  xlate     ToPat         ToDate 
 * Todate is 03/29/06

My first thought was to use the same technique with SQL/400’s TRANSLATE function, but that was a dead end. TRANSLATE requires that the second and third parameters be constants, which means I can’t use a date field in the second operand. So I wrote my own SQL function. I cleverly changed the L in XLATE to a D and got XDATE for my function name.

Here’s the XDATE source member, which includes RPG’s %XLATE function.

H nomain                                                  
                                                          
D XDate           pr            10a   varying             
D  inExpression                 10a   varying             
D  inToString                    8a   varying             
D  inFromString                  8a   varying             
                                                          
P XDate           b                   export              
D XDate           pi            10a   varying             
D  inExpression                 10a   varying             
D  inToString                    8a   varying             
D  inFromString                  8a   varying             
                                                          
 /free                                                    
   return %xlate(inFromString: inToString: inExpression); 
 /end-free                                                
P XDate           e

Create a module with the XDATE function in it.

CRTRPGMOD MODULE(MYLIB/XDATE)
   SRCFILE(MYLIB/QRPGLESRC) 
   SRCMBR(XDATE)

Create a service program from the module.

CRTSRVPGM SRVPGM(MYLIB/XDATE) 
   MODULE(MYLIB/XDATE) EXPORT(*ALL)

Create an SQL function that runs subprocedure XDATE in service program MYLIB/XDATE.

create function MYLIB/xdate         
 (ToExpression varchar(10),          
  ToString     varchar(8),           
  FromString   varchar(8))           
 returns varchar(10)                 
 returns null on null input          
 deterministic                       
 language rpgle                      
 no sql                              
 parameter style general             
 external name 'MYLIB/XDATE(XDATE)'

Now use the XDATE function to reformat dates any way you want them. The first parameter is the format of the resultant date. The second parameter is the date to be reformatted. If it is numeric, use the DIGITS function to convert it to character. The third parameter is the format of the date as it sits in the database.

Here’s an example that reformats a numeric date in ISO format to several different popular date formats.

select SomeDate,                                           
  xdate('ABCD-EF-GH', digits(SomeDate), 'ABCDEFGH') as ISO, 
  xdate('EF/GH/CD',   digits(SomeDate), 'ABCDEFGH') as MDY,   
  xdate('GH-EF-CD',   digits(SomeDate), 'ABCDEFGH') as DMY,   
  xdate('GH-EF-ABCD', digits(SomeDate), 'ABCDEFGH') as DMYY 
from SomeLib/SomeFile

Here’s the output from the SQL query.

SOMEDATE     ISO         MDY         DMY         DMYY       
20,051,221   2005-12-21  12/21/05    21-12-05    21-12-2005 
20,051,215   2005-12-15  12/15/05    15-12-05    15-12-2005 
20,051,223   2005-12-23  12/23/05    23-12-05    23-12-2005 
20,051,222   2005-12-22  12/22/05    22-12-05    22-12-2005 
20,060,112   2006-01-12  01/12/06    12-01-06    12-01-2006 
20,051,214   2005-12-14  12/14/05    14-12-05    14-12-2005 
20,060,130   2006-01-30  01/30/06    30-01-06    30-01-2006 
20,060,117   2006-01-17  01/17/06    17-01-06    17-01-2006

The SQL is messier than I would like it to be, but easy to use. Considering that the first column looks like garbage next to columns two through five, it’s worth the effort to me.