Working with Dates

This chapter provides an overview of dates and date arithmetic and discusses how to:

• Use literal date formats.

• Use string-to-date conversions.

• Use date-to-string conversions.

• Use dates with the INPUT command.

• Use date edit masks.

• Declare date variables.

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 between 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 between 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 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 the day, month, and year only. 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.

Using Literal Date Formats

SQR enables you to specify date constants and date values in a special format that is recognized without the use of an edit mask. This is called the literal date format. For example, you can use a value in this format in the strtodate function without the use of an edit mask. This format is independent of any specific database or language preference.

The literal date format is SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]. The first S in this format represents an optional minus sign. If preceded with a minus sign, the string represents a BC date. The digits that follow represent year, month, day, hours, minutes, seconds, and microseconds.

Note. The literal date format assumes a 24-hour clock.

You can omit one or more time elements from the right part of the format. A default is assumed for the missing elements. Here are some code examples:

let $a = strtodate('20040409')
let $a = strtodate('20040409152000')

The first LET statement assigns the date of April 9, 2004 to the $a variable. The default time portion is 00:00. The second LET statement assigns 3:20 in the afternoon of April 9, 2004 to $a. The outputs (when printed with the ‘DD-MON-YYYY HH:MI AM’ edit mask ) are, respectively:

09-APR-2004 12:00 AM
09-APR-2004 03:20 PM

You can also specify a date format with the SQR_DB_DATE_FORMAT environment variable. You can specify this as an environment variable or in the pssqr.ini file.

See Also

Using the PSSQR.INI File and the PSSQR Command Line

Using String-to-Date Conversions

If you convert a string variable or constant to a date variable without specifying an edit mask that identifies the format of the string, SQR applies a date format. This implicit conversion takes place with these commands:

• MOVE.

• The strtodate function.

• The DISPLAY, PRINT, or SHOW commands, when used to format a string variable as a date.

SQR attempts to apply date formats in this order:

1. The format specified in SQR_DB_DATE_FORMAT.

2. The database-dependent format.

3. The SYYYYMMDD[HH24[MI[SS[NNNNNN]]]] literal date format.

Using Date-to-String Conversions

If you convert a date variable to a string without specifying an edit mask, SQR applies a date format. The conversion takes place with:

• The MOVE command.

• The datetostr function.

• The DISPLAY, PRINT, or SHOW commands when used to output a date variable.

SQR attempts to apply date formats in this order:

1. The format specified in SQR_DB_DATE_FORMAT.

2. The database-dependent format.

See Also

PeopleTools 8.52: SQR Language Reference for PeopleSoft

Using Dates with the INPUT Command

The INPUT command also supports dates. You can load a date into a date or string variable. For string variables, use the TYPE=DATE qualifier. Specify a format for the date. Here is a code example:

input $start_date 'Enter starting date'  type=date  format='dd/mm/yyyy'

In this example, the user is prompted with Enter starting date: (the colon is automatically added). The user then enters the value, which is validated as a date by using the dd/mm/yyyy format. The value is loaded into the $start_date variable.

Using Date Edit Masks

When you print dates, you can format them with an edit mask. For example:

print &order_date () edit 'Month dd, YYYY'

This command prints the order date in the specified format. The name of the order date month is printed, followed by the day of the month, a comma, and four-digit year. SQR for PeopleSoft provides an extensive set of date edit masks.

See PRINT.

If the value of the date value being edited is March 14, 2004 at 9:35 in the morning, the edit masks produce the following results:

The result for EY is:

Note. The MON, MONTH, DAY, DY, AM, PM, BC, AD, ER, EY, and RM masks are case-sensitive and follow the case of the mask that is entered. For example, if the month is January, the Mon mask yields Jan and MON yields JAN. All other masks are case-insensitive and can be entered in either uppercase or lowercase.

If the edit mask contains other text, it is also printed. For example:

print &order_date () edit ’As of Month dd, YYYY’

This command prints the As of March 14, 2004 string if the order date is March 14, 2004. Because the words As of are not recognized as date mask elements, they are printed.

A backslash forces the character that follows into the output. This technique is useful to print text that would otherwise be recognized as a date mask element. For example, a mask of The \mo\nth is Month results in The month is March as an output string. Without the backslashes, the output string would be The march is March. The second backslash is needed because n is a valid date edit mask element.

In some cases, combining date edit mask elements can result in ambiguity. One example is the 'DDDD' mask, which could be interpreted as various combinations of DDD (day of year), DD (day of month), and D (day of week). To resolve such ambiguity, use a vertical bar as a delimiter between format elements. For example, DDD followed by D can be written as DDD|D.

In addition, national language support is provided for the following masks: MON, MONTH, DAY, DY, AM, PM, BC, and AD.

See Also

PeopleTools 8.52: SQR Language Reference for PeopleSoft

Declaring Date Variables

To hold date values in your program, use date variables. Like string variables, date variables are prefixed with a dollar sign ($). You must explicitly declare date variables by using the DECLARE-VARIABLE command.

Date variables are useful for holding results of date calculations. For example:

begin-setup
   declare-variable
      date $c
   end-declare
end-setup
...
let $c = strtodate('March 1, 2004 12:00','Month dd, yyyy hh:mi')
print $c () edit 'dd/mm/yyyy'

In this code example, $c is declared as a date variable. Later, it is assigned the value of noon on March 1, 2004. The $c variable is then printed with the dd/mm/yyyy edit mask, which yields 01/03/2004.

Date variables can be initialized with date literals as shown in this example:

begin-setup
   declare-variable
      date $c
   end-declare
end-setup
...
let $c = '20040409152000'

The LET statement assigns 3:20 in the afternoon of April 9, 2004 to $c.