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