Monday, December 28, 2015

SQL - DECODE truncates the date value

I would like to share a tip related to SQL in this blog. I had a PL/SQL package that updates a record in the database table with a DATE value based on some condition.
The Update statement was like :-

UPDATE XX_TAB
SET    xx_updated_date = decode(cur.ship_num,null,null,sysdate)
WHERE  record_id  = cur.record_id;

The intention was to check whether cur.ship_num is NULL or not. If it is NULL, update the date field, xx_updated_date to NULL. Otherwise update the date field with sysdate.
The update was happening properly. But the sysdate value was getting truncated. That means, it is just updating the date, not the time.

Later figured out that, since the first returnable value in the decode is a NULL, an implicit conversion of DATE value to VARCHAR2 happens(when sysdate value is returned by DECODE in this case); and the time value will be lost.
Modified the DECODE statement by adding a TO_DATE and it started working as required.

UPDATE XX_TAB
SET    xx_updated_date = decode(cur.ship_num,null,TO_DATE(null),sysdate)
WHERE  record_id  = cur.record_id;

You can find a detailed discussion related to this topic in the below thread of OTN forum.
https://community.oracle.com/thread/2255091