Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    18

    Unanswered: Date Column - NULL

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Date Column - NULL

    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')
    nn

  3. #3
    Join Date
    Jan 2003
    Posts
    18

    Re: Date Column - NULL

    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')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •