Working with Dates

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

Click to jump to parent topicUnderstanding 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:

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.

Click to jump to parent topicUsing 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

Click to jump to parent topicUsing 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:

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.

Click to jump to parent topicUsing 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:

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

Click to jump to parent topicUsing 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.

Click to jump to parent topicUsing 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:

Edit Mask

Result

Notes

dd/mm/yyyy

14/03/2004

NA

DD-MON-YYYY

14-MAR-2004

NA

'Month dd, YYYY.'

March 14, 2004.

An edit mask containing blank space must be enclosed in single quotes.

MONTH-YYYY

MARCH-2004

The name of the month in uppercase, followed by the 4-digit year.

HH:MI

09:35

NA

'HH:MI AM'

09:35 AM

Meridian indicators. An edit mask containing blank space must be enclosed in single quotes.

YYYYMMDD

20040314

NA

DD.MM.YY

14.03.99

NA

Mon

Mar

The abbreviated name of the month.

Day

Thursday

The day of the week.

DY

THU

An abbreviation for the day of the week.

Q

1

Quarter.

WW

11

The week of the year.

W

2

The week of the month.

DDD

74

The day of the year.

DD

14

The day of the month (1–31).

D

3

The day of the week (Sunday is 1).

EY

Please see below

The Japanese imperial era (Meiji, Taisho, Showa, Heisei).

ER

16

The year in Japanese imperial era.

The result for EY is:

Japanese Imperial Era

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

Click to jump to parent topicDeclaring 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.