Re: SQL Question: Date Substitution on NULL fields

try this . . .

SELECT purchase_no, DECODE(date,NULL,'00/00/00', TO_CHAR(date)) FROM 'table'
or
SELECT purchase_no, NVL(date,'00/00/00') FROM 'table'

David Sivick wrote:

> 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
>
> I have tried to fool the replace function, but since I am not dealing
> with a
> character field, it is not being freindly.
>
> Thanks,
> David

--
eric kamradt
hm: (801) 567-0912
mailto:kamradt_at_inconnect.com
http://www.inconnect.com/~kamradt
http://www.inconnect.com/~kamradt/resume.html