There are two main ways to store and use dates in SQR:
- Store them as a character string in a string variable OR
- Store them as date variables
Both types of variables (strings and dates) use the $ dollar sign prefix but dates are declared differently to strings.
So why use a date variable instead of a string? Well the main reason is if you want to perform any date based calculations. If just need to store a date in a variable then using a string is fine. However if you wanted to perform a calculation like adding 7 days to a date then a date variable is the way to go. Date variables are also required when using the delivered SQR date functions.
Declaring a date variable
Date variables are declared in the setup section like this:
begin-setup
declare-variable
date $dtVariable
end-declare
end-setup
Note that date variables are initialized to null when they are declared.
Date Functions
Now that you have a date variable, there a number of delivered date functions you can use in SQR including:
- strtodate() to convert a string to date
- datetostr() to convert a date to a string
- dateadd() to add to a date
- datediff() to subtract dates
- datenow() to get teh current date
The following are examples of how to use dateadd() and datediff(), probably the two most frequently used functions:
Example 1: using datediff()
Return the number of days between the column variable, &EFFDT and the variable $BaseDate:
let #nbrOfDays = datediff(&EFFDT, $BaseDate, 'DAYS')
Note that the options available for the return period include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.
Example 2: using dateadd()
Add four weeks to the $dtFinish variable:
let $dtFinish = dateadd($dtStart, 'WEEK', 4)
