Re: Testing for datatype in columns?

Try to use stored sql functions like

function test_date(is_it_a_date varchar2) return integer is   d date;
begin
  d := to_date(is_it_a_date);
  return 1;
exeception
  when value_error then
    return 0;
end;

Then:
select doc_num, issue_date
 from document
 where test_date(issue_date) = 0;

I didn't test it!

Hope this will help
Andreas Prusch

In article <6om9sd$cm6$1_at_nnrp1.dejanews.com>,   rune.slinning_at_bigfoot.com wrote:
> Hi All!
>
> I have a table DOCUMENT with the following columns:
>
> doc_no varchar2(30),
> revision varchar2(30),
> issue_date varchar2(30)
>
> I need a query to check if the content of the revision column is numeric and
> the content of the issue_date is a date.
>
> I would need a query with similar functionality as the ones below:
>
> select doc_num, revision
> from document
> where revision is not numeric;
>
> select doc_num, issue_date
> from document
> where issue_date is not date;
>
> Thanks,
> Rune
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Jul 17 1998 - 01:24:54 CDT