Dates in SQR

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.

Date variables are declared in the setup section like this:

begin-setup
    declare-variable
        date $dtVariable
    end-declare
end-setup

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

Example using datediff()

Return the number of days between the column variable, &EFFDT and the variable $BaseDate:

let #nbrOfDays = datediff(&EFFDT, $BaseDate, 'DAYS')

Example using dateadd()

Add four weeks to the $dtFinish variable:

let $dtFinish = dateadd($dtStart, 'WEEK', 4)

The options available for the period include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.