Omit Commas from Numeric Dates
March 1, 2006 Ted Holt
|
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. |
