On Dec 31, 6:45 am, Swapna <[email protected]> wrote:
> Hi Friends,
>
> I need to verify the date formats of a table that contains columns of
> varchar2.
>
> The table contains date like
>
> AIRED_TIME | BROADCAST_TIME
> 2009-12-16 | 2009-16-12
> 2009-12-15 | 2009-15-12
> 14-12-2009 | 2009-14-12
> 2009-12-13 | 2009-13-12
> 2009-12-11 | 2009-12-11
>
> Now, in the table i need to check whether the columns are containg valid
> date types. and fetch those records and delete the other records.
>
> As the high lighted records are not valid.
>
> Kindly reply with solutions.
>
> Regards,
> Swapna
I would write a little function that verified that the input string
could be converted using a particular format. Bear in mind that this
is NOT actual code (it IS NY eve)
create or replace Function check_date(date_string in varchar2) return
number as
tempvar number
begin
tempvar := to_date(date_String,'yyyy-mm-dd');
return 1;
exception
when others then -- this will return 0 on ANY error, you might want
to be more specific
return 0;
end;
and use it such as ...
delete from a_table where check_date(a_column) = 0
If you want to make it more robust, you can pass in the format string
and capture other errors as well. The technique is also handy for
dealing with columns of varchars that could be in one of several date
formats (ETL code on messy data often needs this)
Again, do not just type this in and expect it to work, it is just a
sketch of a solution and there ARE syntax errors in it.
Rob
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
