The Power of the Oracle SIGN Function and Dates

Although I know this has been written about before, I thought it would still be worthwhile posting how the SIGN PL/SQL function can be inlined within a query to produce some really cool results when dealing with dates. We have a table that tracks a start date and end date for enrollments in our application.

The following table structure is more or less what we use:
SQL> desc APPLICATION_ENROLLMENT; 
Name                                      Null?    Type 
----------------------------------------- -------- ---------------- 
ENROLLMENT_ID                             NOT NULL NUMBER 
USER_ID                                   NOT NULL NUMBER 
REC_START_DT                                       DATE     
REC_END_DT                                         DATE

When someone is currently enrolled we set the end date to 12/31/9999.  So a very simple query might want to determine which enrollments are currently active.  This is easy enough as you can simply query out all of the records whose enrollment end dates are sufficiently in the future (as an aside, we could have also opted to use NULL values in the end date but felt using an actual value would be more efficient for indexing purposes for queries that operate off of both dates when searching for ranges of records that match (e.g., show me all enrollments between these two dates).

e.g., SELECT * FROM APPLICATION_ENROLLMENT WHERE REC_END_DT > sysdate + 365

Yet there are times when we have found that we want in the query to select out a value that is based on determining whether the enrollment is active.  Our most recent example came up while trying to satisfy a question regarding how many of our enrollments started or ended in the month of December.  We came up with the following very simple query that used the SIGN function to both filter out end dates that were in the future (because you might get a false positive for the month of December) and that allowed us to count the total number of matching enrollments.

select sum( decode( to_number(to_char(rec_start_dt, 'MM')) , 12, 1, 0, decode( SIGN( rec_end_dt - (sysdate + 365) ), 1, 0, decode( to_number(to_char(rec_end_dt, 'MM')), 12, 1, 0) ) ) ) december_match from tracked_offender_device

By using the SIGN function we were able to translate the end date into a value (indicating whether it was in the future or not) that that we could then feed to a DECODE such that we could filter those end dates out. The rest of the query simply evaluates whether either the start date or remaining end dates occur in the month of December and if either do it returns a '1' that can then be summed to produce the result.