DOCUMENT #717 ======================================================================= MANIPULATING DATES AND TIMES ======================================================================= PRODUCT: R:BASE VERSION : 3.1 or Higher ======================================================================= CATALOG: Data Manipulation AREA : Date & Time ======================================================================= R:BASE offers a number of functions to manipulate data and time values in addition to the standard date and time arithmetic. By working with date and time data you can calculate ages, elapsed time, add months and years, etc. The current date and time are always available in R:BASE in the system variables #DATE and #TIME. Date arithmetic =============== There is a specific set of arithmetic operations that can be performed on date values. You can add or subtract a specified number of days to a DATE value returning a DATE. You can subtract one DATE value from another returning a number of days (INTEGER). All other date operations are done through using one of the date functions. Often a combination of date arithmetic and date functions are used to perform data manipulations on dates. DATE1 + days = DATE2 DATE1 - days = DATE2 DATE1 - DATE2 = days Date arithmetic can be used in computed columns, form and report expressions, custom program code, and WHERE clauses. One use for date arithmetic is calculating a person's age. Use the expression ((.#DATE -birth_date)/365.25) Subtract the person's birthdate from today's date returning the number of days between the two dates. Divide the number of days by 365.25 (the number of days in a year) to return the person's age in years, 365.25 is used to account for leap years. Date arithmetic can also be used to calculate callback dates. To callback in 2 weeks (14 days) use the expression (call_date +14) The expression adds 14 days to the original date value and returns the date on which to make the callback. Date Functions ============== Date functions can be used in computed columns, form and report expressions, custom program code, and WHERE clauses. RDATE ----- The RDATE function is used to convert integer values for the month, day and year to an R:BASE DATE data type. All three values are required. If any are zero the result is a NULL; zero is not a valid value for a day, month or year. The RDATE function also requires a 4-digit year value. The arguments of the function can be literal values, variables or expressions, but they must be INTEGER. For example: SET VAR vdate = (RDATE(6,19,1993)) returns the date value 6/19/93 SET VAR vday INTEGER=19, + vmonth INTEGER=6, + vyr INTEGER=93 SET VAR vdate = + (RDATE( (.vmonth+1),.vday,(.vyr+1900) ) ) returns 7/19/93 JDATE ----- The JDATE function converts a DATE data type to a Julian date (1900-1999 only). A Julian date displays a date in the form YYDDD, the last 2 digits of the year followed by the day of the year (1 through 365). The argument of the function can be a literal value, variable or expression; but must be a DATE. For example: SET VAR vjdate = (JDATE('1/1/93')) returns 93001 SET VAR vdate DATE = '6/19/93' SET VAR vjdate = (JDATE(.vdate)) returns 93170, 6/19 is the 170th day of 1993 A Julian date can be converted to a regular DATE data type by using a conversion program from our UDF pack #3, or by writing custom code in R:BASE. The custom code uses string manipulation functions and the RDATE function. Here's an example of code that prompts for a date value, converts it to a Julian date and then converts the Julian date back to a Gregorian date. CLE ALL VAR FILLIN vdate1 USING 'Enter a date ' SET VAR vjdate = (JDATE(.vdate1)) SET VAR vjdate_text = (CTXT(.vjdate)), + vyr = (SGET(.vjdate_text,2,1)), + vday = (SGET(.vjdate_text,3,3)) SET VAR vyr INTEGER, vday INTEGER SET VAR vdate2 = + ((RDATE(01,01,(.vyr+1900))) + (.vday-1) ) WRITE 'Date 1: ',.vdate1, + ' Julian date: ',.vjdate,' Date 2: ',.vdate2 IDAY ---- The IDAY function extracts the day of the month from a DATE value. The argument of the function can be a literal value, variable or expression; but must be a DATE. For example: SET VAR vday = ((IDAY('6/19/93')) returns the value 19 SET VAR vday = (IDAY('06/19/93') + 7) returns the value 26 IDWK, TDWK ---------- The IDWK and TDWK functions are used to calculate the day of the week a particular DATE value falls on. IDWK returns the day of the week as an INTEGER, with Monday =1 and Sunday = 7. TDWK returns the TEXT day of the week. The argument of the function can be a literal value, variable or expression; but must be a DATE. For example; SET VAR vweekday = (IDWK('6/19/93')) returns 6 SET VAR vweekday = (TDWK('6/19/93')) returns Saturday IMON, TMON ---------- The IMON and TMON functions extract the month from a DATE value. IMON returns the INTEGER representation of the month, January =1; TMON returns the TEXT value of the month. The argument of the function can be a literal value, variable or expression; but must be a DATE. For example: SET VAR vmonth = (IMON('6/19/93')) returns 6 SET VAR vmonth = (TMON('6/19/93')) returns June Medical offices often send birthday cards to patients. Using the IMON function, labels can easily be printed for everyone with a birthday this month. PRINT cards WHERE (IMON(birth_date)) = (IMON(.#DATE)) IYR --- The IYR function extracts the year from a DATE value. It returns either a 2-digit or 4-digit value depending on the setting of the DATE FORMAT. The argument of the function can be a literal value, variable or expression; but must be a DATE. SET DATE FORMAT MM/DD/YY SET VAR vyr = (IYR('6/19/93')) returns 93 SET VAR vyr = (IYR('6/19/1993')) returns 93 SET DATE FORMAT MM/DD/YYYY SET VAR vyr = (IYR('6/19/93')) returns 1993 SET VAR vyr = (IYR('6/19/1993')) returns 1993 To locate all employees who turn 65 sometime this year you could use the IYR function in the following command: SELECT empid, empfname, emplname + FROM employee + WHERE ( IYR(.#DATE) - IYR(birth_date) ) = 65 Time arithmetic =============== There is a specific set of arithmetic operations that can be performed on time values. You can add or subtract a specified number of seconds to a TIME value returning a TIME. You can subtract one TIME value from another returning a number of seconds (INTEGER). All other time operations are use one of the time functions. Often a combination of time arithmetic and time functions is used to perform data manipulation. Time can be displayed in either a 24 hour or AM/PM format; time arithmetic and time functions can be done with either format, but is best done using the 24 hour format. TIME1 + seconds = TIME2 TIME1 - seconds = TIME2 TIME1 - TIME2 = seconds Time arithmetic can be used in computed columns, form and report expressions, custom program code, and WHERE clauses. Time arithmetic is often used to calculate elapsed time. The difference between two times is a number of seconds. Divide the number of seconds by 60 to find minutes, and the minutes by 60 to find hours. See the article "Calculate Elapsed Time" in the January/February 1993 Exchange Technical Journal for details on calculating elapsed time -- Document #681 on our automated FAX server (1-206-649-2789). Time functions ============== Time functions can be used in computed columns, form and report expressions, custom program code, and WHERE clauses. RTIME ----- The RTIME function converts integer values representing hours, minutes and seconds to a TIME data type. The RTIME function is used to convert a number of seconds into an hours:minutes:seconds format. You do not need values for all arguments of the RTIME function, some can be zero. Zero can be a valid value for hours, minutes or seconds. The arguments of the function can be literal values, variables or expressions, but they must be INTEGER. For example: SET VAR vtime =(RTIME(10,32,57)) returns 10:32:57 or 10:32AM SET VAR vtime =(RTIME(14,23,0)) returns 14:23:00 or 2:23PM SET VAR vseconds = + (.vstart_time - .vend_time) SET VAR vtime = + (RTIME(0,0,.vseconds) returns elapsed time in HH:MM:SS IHR --- The IHR function extracts the hour portion of a TIME value. The hour is returned as an INTEGER. Hours are always returned in 24 hour format, 2:00AM returns 2, 2:00PM returns 14. The argument of the function can be a literal value, variable or expression; but must be TIME. For example: SET VAR vhr = (IHR(12:15:30)) returns 12 IMIN ---- The IMIN function extracts the minutes portion of a TIME value. The minutes are returned as an INTEGER value. The argument of the function can be a literal value, variable or expression; but must be TIME. For example: SET VAR vminutes = (IMIN(12:15:30)) returns 15 ISEC ---- The ISEC function extracts the seconds portion of a TIME value. The seconds are returned as an INTEGER value. The argument of the function can be a literal value, variable or expression; but must be TIME. For example: SET VAR vsec = (ISEC(12:15:30)) returns 30