Decode function in oracle database

Question

Can anyone please explain the below SQL statement from Oracle DB:

select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','1') from dual

Answer 1

First of all, let's start with the to_char. to_char(SYSDATE,'Day') will give you the day of the week it is today. to_char lets you convert a date (in this case today's date since you've specified sysdate) into a string of a certain format. Take a look here for some other examples of date formats you can use:

http://www.techonthenet.com/oracle/functions/to_char.php

trim removes leading and trailing whitespace.

Now for the decode. You can think of decode like an if else statement. Take a look at:

http://www.techonthenet.com/oracle/functions/decode.php

In your particular example, you could read this statement as: if today is Monday return 3 else return 1.

decode also allows you to do things a bit more complex like this:

select decode(TRIM(to_char(SYSDATE,'Day')),'Monday','3','Tuesday',5,'1') 
from dual

This would read: if today is Monday return 3, else if today is Tuesday return 5, else return 1