[senthil5_kumar]

Hi,
I have a column of Date data type which can accept NULL.
when I query on this column I like to get 'Nil' if the Column is NULL.
So I tried ......
select NVL(date_of_expiry,'Nil')
I got the following error message.
ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone please tell me how to return a string if the date column is NULL.
Thanks in advance.

Regards,
Senthil

[NoviceNo1]

Originally posted by senthil5_kumar
Hi,
I have a column of Date data type which can accept NULL.
when I query on this column I like to get 'Nil' if the Column is NULL.
So I tried ......
select NVL(date_of_expiry,'Nil')
I got the following error message.
ORA-01858: a non-numeric character was found where a numeric was expected

Can anyone please tell me how to return a string if the date column is NULL.
Thanks in advance.

Regards,
Senthil

Hi Senthil,
You are getting this error because your column date_of_expiry
is defined as DATE in the table and the value 'Nil' is not a valid
date.

However you can try this, it will work.

SELECT NVL(TO_CHAR(date_of_expiry),'Nil')

[senthil5_kumar]

Thanks a Lot!!!!!!!!!!!

Regards,
Senthil

Originally posted by NoviceNo1
Hi Senthil,
You are getting this error because your column date_of_expiry
is defined as DATE in the table and the value 'Nil' is not a valid
date.

However you can try this, it will work.

SELECT NVL(TO_CHAR(date_of_expiry),'Nil')