Re: SQL Question: Date Substitution on NULL fields

Hi David,

> How can I get the null fields in a column type DATE to be represented
> by some other field in a select statement. I don't want to update
> the fields in the table, I just want the field to show up as
> something else in the select.
>
> For Example:
>
> Purchase_No Item Date
> ------------------------------------------------
> 0145 Boots
> 0146 Socks 01/01/97
> 0147 Tie 11/11/96
>
> So my select would be:
>
> select purchase_no,date from 'table'
>
> And I want to get back...
>
> 0145 00/00/00
> 0146 01/01/97
> 0147 11/11/96

If you use SQL Plus, try column formating like this:

column date1 null '00/00/00'
select purchase_no,date date1 from 'table';

More general would be the use of nvl function:

select purchase_no,nvl(to_char(date,'DD/MM/YY'),'00/00/00') from 'table';

Regards
Jan Received on Tue Aug 05 1997 - 00:00:00 CDT