Oracle: Store date in a BLOB field and retrieve as date when needed

Question

I am using this expression to store a date directly in a BLOB field:

utl_raw.cast_to_raw(sysdate)

This works fine, but when I need to retrieve this date, I can't manage to retrieve the time as well, only the date, with the function below.

utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(sysdate))

Is there a way to retrieve the date I stored with time?

PS: I know I can just convert the date to a varchar with TO_CHAR and datetime format before storing it, but let's assume I can't do it right now.

Answer 1

utl_raw.cast_to_raw accepts a string, not a date, therefore what you are capturing is effectively to_char(sysdate). This will use the current nls_date_format which is typically something like DD-MON-RRRR (and nls_date_language which might be English but could vary depending on user desktop settings). You'll need to either change the default or specify a different format, e.g.

utl_raw.cast_to_raw(to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'))