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:

        date $dtVariable
NOTE: date variables are initialized to null when they are declared.

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.

No Comments
Back to top