Should I ever use the current date in SQL?

If you are using the “current date” in SQL for application engines or online pages, then you are probably making a mistake in your design. This includes:

• %currentDateIn MetaSQL
• getdate() SQLServer Function
• sysdate oracle function
• %date PeopleCode system variable

I have been working with payroll and accounting systems for well over a decade. I have seen so many bugs with interfaces and processes that are the result of poor use of effective dated logic. If you are working with effective dated tables at the transaction or setup level, using the system date is never a good idea. You always want to get the data based around some “anchor date.” This gives the end user much more flexibility when retrieving data from the database.

A Payroll Example

If you are working with Payroll, it is extremely important that you be able to do retroactive and future dated payrolls. In normal day-to-day payroll processing, future dated processing is the norm. There are many reasons for this. If you want to get direct deposits out to the employees by the end of the month, you have to close the payroll out several days in advance.

The typical usage of effective dated logic in this type of processing is geared around “Pay End Date” as the anchor date. So the SQL logic may pull the employee’s salary as of the “Pay End Date” and not the current date. If the system were to use the current date, it may pull the salary as of the run date of the SQL. If you were processing future or retro-payrolls this would be a huge mistake. The proper thing to do is to look up all effective dated data based on some “anchor date” which for payroll processing this typically is the pay period end date. If you used the system date or run date, this could cause major accounting problems.

An HR Example

Let’s say you have a user who is requesting a report that will generate the total number of active employees by department. One quick and dirty approach would be to quickly put together a query based on the system date and give it back to the user. However, this is probably not what the user wanted even if they did not specifically ask for it. What they really wanted from you was a date prompt which would allow them to run the query for different sets of dates. Let’s say HR wanted to find out the head count of the departments last year and compare it to this year. The proper thing to do would be to:

• Give the user a date prompt.
• Code the SQL to pull the active employee relative to that date.

This allows the user to run the query for different dates and compare the results. It would also let them do future dated queries. HR typically will have future dated transactions like hires and even terminations. They may want to see the headcount based on some future date after a re-organization, merger or mass load.

One other thing to note here is that you also want to grab the department fields like description relative to the anchor date the user input in the prompt. This will allow the user to see what the department was called in the past or what it will be called in the future not what it is called at run time. Query Manager will default to the current date which you will need to change to your prompt.