Can we store anything other than a valid date or null value in a date field?

Question

I'm working on Oracle and Perl. I have three fields in a table (A, B, C), and I would like to form, for every row in the table, a string a_b_c using "join". C refers to a date. It can take null values. In that case , this "join" will return a warning "use of uninitialized values in join". I know that i have to select nvl(C,something) so that I get a_b_something when C is null. Can you suggest me what that "something" can be, if I want to distinguish between these rows and other rows.

In short, can I store anything other than a valid date or null value in a date field?

Answer 1

I know that i have to select nvl(C,something) so that I get a_b_something when C is null

Selecting isn't storing the value in the column, and Oracle actually passes datetimes back to clients as strings, so it's entirely possible that you can just use NVL(col, '') without confusing DBI at all.

If that doesn't work out, you can always just build the whole thing up in SQL: just select

A || '_' || B || '_' || (CASE WHEN C IS NULL THEN '' ELSE TO_CHAR(C) END)

as one of your columns.

Answer 2

As OMG Ponies said, you can't store a non-date in a DATE field in Oracle.

As hobbs intimated, you can use NVL to convert a null date into a string - for example, NVL(TO_CHAR(C),'NULLDATE') would return the string 'NULLDATE' if the date is null.