SQL DayOfWeek Functions for Non-Date Data

Hey, Ted:

Our database stores all dates as numeric fields. Is there a way in SQL to convert a numeric date field (YYYYMMDD) into a date to use the day of week functions?

--David

If you know that there are, and never will be, any invalid dates in your database, you can use the digits and substring functions to convert a numeric field to the date data type so the DayOfWeek and DayOfWeek_ISO functions will recognize it.

select jedate,                                                    
   dayofweek_iso(
      date(substr(digits(jedate),1,4) concat
           '-' concat
           substr(digits(jedate),5,2) concat
          '-' concat               
           substr(digits(jedate),7,2))
   )
 from gljnly                                                       

But this is an awful lot of keying that must be done in every SQL statement that needs the function, and invalid data will mess things up. I suggest you write an SQL function that will perform the conversion and return null when it receives an invalid date argument. Here's one that I call zzDayOfWeekISO.

create function mylib/zzDayOfWeekISO 
   (d decimal (8))                    
   returns smallint                   
   language SQL                       
   contains SQL                       
   deterministic                      
    returns null on null input                      
    no external action                              
  begin                                             
    declare dig char (8);                           
    declare exit handler for sqlstate value '22007' 
      return null;                                  
    set dig = digits(d);                            
    return dayofweek_iso(                           
       date(substr(dig,1,4) concat '-' concat       
            substr(dig,5,2) concat '-' concat       
            substr(dig,7,2)));                      
  end

I keyed this code into an interactive SQL/400 session. You should also be able to store it in a source physical file member and execute it with the Run SQL Statement (RUNSQLSTM) command. You could also use iSeries Navigator or a non-IBM product like Howard Arner's SQLThing package.

Once you've created the function, you will be able to use it in SQL queries. In the following example, field JEDATE is a packed decimal field in YYYYMMDD format.

select jedate, zzDayOfWeekISO(jedate)
  from gljnly                        

Here's the output of the query. The last three rows contain invalid data values of 0, all 9s, and February 29, 2003, respectively. Notice that invalid dates caused the function to return null, which is indicated by a hyphen (-).

    JEDATE  ZZDAYOFWEEKISO
2003-01-01           3 
2003-02-01           6 
2003-03-01           6 
2003-08-01           5 
2003-09-01           1 
2003-09-15           1 
                      - 
9999-99-99            - 
2003-02-29            -

This function will also work for zoned decimal and binary fields. SQL will convert zoned and binary data to the proper format when the function runs.

--Ted

Sponsored By WORKSRIGHT SOFTWARE

Do you need area code information? Do you need ZIP Code information? Do you need ZIP+4 information? Do you need city name information? Do you need county information? Do you need a nearest dealer locator system? We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you! The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year. PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal. Just call us and we'll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4. WorksRight Software, Inc. Phone: 601-856-8337 Fax: 601-856-9432 E-mail: software@worksright.com Web site: www.worksright.com