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.

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)
sqr
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License