Back Issues
The Director's Chair
Advanced Technique
From FOCUS to WebFOCUS
Feature Preview
New Feature
FOC101
by Noreen Redden
Feature
FOCUS – Then and Now
Releases Currently Shipping and Latest Certifications
User Group Meeting Schedule

Using Date-Time Subroutines

In the last issue of TABLE FILE CAR we discussed Date subroutines for dealing with “traditional” dates and Smart Dates. Now, we'll consider fields that contain not just digits representing a date (A6YMD) or displacements in days from a base-date (MDY), but actual displacements of both date and time from a base. For this we need a suite of subroutines to add, convert, subtract, etc., not just dates, but dates and times. In this article, we'll look at a few of the more common ones, as well as their practical applications.

For most of us, a date-time is a field in a data file, and its USAGE and ACTUAL definitions in the Master File Description and the data file type, tell FOCUS how the data is stored and how it is to be displayed. If you need an alpha literal in a date-time stamp, you can store the value using either HINPUT or DT. HINPUT is more useful if the value is contained in an alpha character string, but if the value is a natural date, DT is more useful.

DT1/HYYMDS = HINPUT (14, '200006260545000000', 8, 'HYYMDS'); 
DT2/HYYMDS = DT (20051226 05:45); 
DT3/HYYMDS = DT (2005 DEC 26 05:45); 
DT4/HYYMDS = DT (December 26 2005 05:45);

Most of the subroutines for manipulating dates have parallels in the date-time arena. For instance, DATEADD adds components (year/month/day) to a date. HADD performs the same function for date-time variables. DATECVT converts a date from one format to another; HCNVRT converts a date-time to an alphanumeric value; HTIME converts only the time portion, and HDATE converts only the date portion.

DATEDIFF subtracts two dates, and returns the number of years, months, or days. HDIFF again performs the same function, but with date/times. Once you have the number of minutes, for instance between two times, you can easily convert that for display as hours/minutes/seconds. 

DEFINE FUNCTION DHHMMSS/A12 (SECONDS/D15) 
SECSINDAY/I9 = 60 * 60 * 24; 
SECSINHOUR/I9 = 60 * 60; 
DAYS/I2 = SECONDS/SECSINDAY; 
HRS/I2 = (SECONDS - (DAYS * SECSINDAY)) / SECSINHOUR; 
MINS/I2 = (SECONDS - (DAYS * SECSINDAY) - (HRS * SECSINHOUR)) / 60; 
SECS/I2 = SECONDS - (DAYS * SECSINDAY) - (HRS * SECSINHOUR)  - (MINS * 60); 
DHHMMSS/A12 = FTOA(DAYS,'(F2S)','A2') | 'D ' | FTOA(HRS,'(F2)','A2') 
     | ':' | FTOA(MINS,'(F2)','A2') | ':' | FTOA(SECS,'(F2)','A2'); 
END 
 
TABLE FILE STATS1
 PRINT END_DATE AS 'END' 
 COMPUTE DIFF1/D15 = HDIFF(DT3, DT2, 'SECOND', 'D15');NOPRINT 
  COMPUTE DIFF1A/A12 = DHHMMSS(DIFF1); AS DIFFERENCE 
 BY DT2 NOPRINT 
  HEADING 
 "START DATE: <DT2 " 
  END    

START DATE: 2005/12/26 05:45:05 


END                 DIFFERENCE   
---                 ---------- 
2006/01/05 03:22:15 9D 21:37:10    
2005/12/26 15:22:42  D  9:37:37

The subroutines HNAME and HPART return a COMPONENT of the date-time variable in either alphanumeric or numeric format. For instance, you can determine the Week of the year for a given date: 

WEEKNUMBER/a10 = HNAME(transdate,'WEEK','A10');

You set WEEKFIRST to specify the day of the week that should be considered as initiating the week change. For instance, you can specify that a week starts on Monday, in which case any dates in a year prior to the first Monday, would be considered in WEEK 0. If you wish to have them considered part of the prior year, before you use HNAME or HPART, move the date to the prior Monday.

This, of course, only scratches the surface of the flexibilities FOCUS offers for handling dates and new routines are added all the time, so be sure to check the users manual, help topics, and new features if you need to make a date.

Back Next