Effective Dated SQL Definitions

An interesting property with standalone SQL definitions (not record-view SQL or application engine SQL) is that you can make them effective dated.

This is available in the Advanced tab when you look at the SQL properties (where you specify the description and comments). To enable effective dates, check the Show Effective Date check box.

Once enabled, an effective date option is available in the SQL definition and you can add new entries as shown.

effdt-sql-definition.png

As expected, the latest effective dated SQL definition less than or equal to the current date will be used. So you can have future effective dated rows that take effect from a certain date.

One of the benefits of this is that it lets you keep versions of your SQL, and you can track what changes were made over time.

There are a couple of drawbacks:

  1. The first effective dated row will be set to 01/01/1900 and not the date the SQL was created
  2. It doesn't seem to be possible to delete existing effective dated rows
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License