At work I recently had a simple need. I had a table with data. One of the columns is a date-time stamp – lets call it “Last_Login_Date”. I wanted a “Boolean” column that was a false if the value was less than 60 days old and true if it was older than 60 days. (Yes, I know that Oracle does not have a Boolean data type so how about Zero (0) for false and One (1) for true, OK?)
Ok, you would think that this would be simple: Just add the calculation in the select list, right? SYSDATE - Last_Login_Date > 60 AS Is_Old_Account.
But no, nothing can be easy can it.
With a fairly cryptic combination of Oracle commands I replicated what I wanted. Here it is:
DECODE(GREATEST(FLOOR(SYSDATE – NVL(LAST_LOGIN_DATE, TO_DATE('2000/01/01', 'yyyy/mm/dd')), 60), 60, 0, 1) AS IS_OLD_ACCOUNT
And, here is the breakdown:
First NVL incase the field had never been populated.
Next, FLOOR to turn the difference into an integer (probably not needed, but during debugging, having an integer was easier to deal with.
Then, GREATEST takes two values, the date difference and my max value. So, if the date difference is less than 60, the GREATEST function will return 60.
Lastly DECODE will look at the value and say – If it is 60 return 0, else return 1.
So, the end result is if the value is 60 or less, the value is 0 else it is 1.
Note: I could have ran the SQL twice with a JOIN command and add to the where clauses the filters of SYSDATE - Last_Login_Date > 60 and SYSDATE - Last_Login_Date <= 60 and a dummy column in the select list added in out “Boolean” column IS_OLD ACCOUNT, but this did not seem very efficient.
