Understanding Dates and Date Arithmetic

SQR has powerful capabilities in date arithmetic, editing, and manipulation. A date can be represented as a character string or in an internal format by using the SQR date data type.

The date data type enables you to store dates in the range of January 1, 4712 BC to December 31, 9999 AD. It also stores the time of day with the precision of a microsecond. The internal date representation always keeps the year as a four-digit value. Keep dates with four-digit year values (instead of truncating to two digits) to avoid date problems at the turn of the century.

You can obtain date values:

  • By selecting a date column from the database.

  • By using INPUT to get a date from the user.

  • By referencing or printing the $current-date reserved variable.

  • By using the SQR date functions dateadd, datediff, datenow, or strtodate.

  • By declaring a date variable using the DECLARE-VARIABLE command.

For most applications, you do not need to declare date variables. Date variables are discussed later in the section.

Many applications require date calculations. You may need to add or subtract a number of days from a given date, subtract one date from another to find a time difference, or compare dates to determine whether one date is later, earlier, or the same as another date. SQR enables you to perform these calculations in your program.

Many databases enable you to perform date calculations in SQL, but doing so can be difficult if you are trying to write portable code because the syntax varies among databases. Instead, perform those calculations in SQR; your programs will be portable because they will not rely on a particular SQL syntax.

The dateadd function adds or subtracts a number of specified time units from a given date. The datediff function returns the difference between two specified dates in the time units that you specify: years, quarters, months, weeks, days, hours, minutes, or seconds. Fractions are allowed; you can add 2.5 days to a given date. Conversion among time units is also allowed; you can add, subtract, or compare dates by using days and state the difference by using weeks.

The datenow function returns the current local date and time. In addition, SQR provides a reserved date variable, $current-date, which is automatically initialized with the local date and time at the beginning of the program.

You can compare dates by using the usual operators (< , =, or >) in an expression. The datetostr function converts a date to a string. The strtodate function converts a string to a date.

The following sample program uses functions to add 30 days to the invoice date and to compare it to the current date:

begin-select
order_num     (,1)
invoice_date
   if dateadd(&invoice_date,'day',30) < datenow()
      print 'Past Due Order' (,12)
   else
      print 'Current Order'  (,12)
   end-if
   position (+1)
end-select

This code example uses the dateadd and datenow functions to compare dates. The dateadd function adds 30 days to the invoice date (&invoice_date). The resulting date is then compared with the current date, which is returned by datenow. If the invoice is older than 30 days, the program prints the Past Due Order string. If the invoice is 30 days old or less, the program prints the Current Order string.

To subtract a given number of days from a date, use the dateadd function with a negative argument. This technique is demonstrated in the next code example. In this example, the IF condition compares the invoice date with the date of 30 days before today. The condition is equivalent to that of the previous code example.

   if &invoice_date < dateadd(datenow(),'day',-30)

You can also write this condition as follows by using the datediff function. Note that the comparison is now a simple numeric comparison, not a date comparison:

   if datediff(datenow(),&invoice_date,'day') > 30

All three IF statements are equivalent, and they demonstrate the flexibility that is provided by these functions.

Here is another technique for comparing dates:

begin-select
order_date
   if &order_date > strtodate('3/1/2004','dd/mm/yyyy')
     print 'Current Order' ()
   else
     print 'Past Due Order' ()
   end-if 
from orders
end-select

The IF statement has a date column on the left side and the strtodate function on the right side. The strtodate function returns a date type, which is compared with the &order_date column. When the order date is later than January 3, 2004, the condition is satisfied. If the date includes the time of day, the comparison is satisfied for orders of January 3, 2004, with a time of day greater than 00:00.

In the next code example, the date is truncated to remove the time-of-day portion of a date:

if strtodate(datetostr(&order_date,'dd/mm/yyyy'),'dd/mm/yyyy') > 
       strtodate('3/1/2004','dd/mm/yyyy')

In this code example, the datetostr function converts the order date to a string that stores only the day, month, and year. The strtodate function then converts this value back into a date. With these two conversions, the time-of-day portion of the order date is omitted. Now when it is compared with January 3, 2004, only dates that are of January 4 or later satisfy the condition.