Create a function to convert string type variable to date type variable : Table of Type « PL SQL « Oracle PL / SQL






Create a function to convert string type variable to date type variable

    
SQL>
SQL> create or replace
  2  function my_to_date( p_string in varchar2 ) return date
  3  as
  4      type fmtArray is table of varchar2(25);
  5
  6      l_fmts  fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
  7                                    'dd/mm/yyyy',
  8                                    'dd/mm/yyyy hh24:mi:ss' );
  9      l_return date;
 10  begin
 11      for i in 1 .. l_fmts.count
 12      loop
 13          begin
 14              l_return := to_date( p_string, l_fmts(i) );
 15          exception
 16              when others then null;
 17          end;
 18          EXIT when l_return is not null;
 19      end loop;
 20
 21      if ( l_return is null )
 22      then
 23          l_return :=
 24             new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
 25                       p_string, 'GMT', 'EST' );
 26      end if;
 27
 28      return l_return;
 29  end;
 30  /

Function created.

SQL>
SQL> select my_to_date('12-02-2008') from dual;
MY_TO_DATE('12-02-20
--------------------
12-FEB-2008 00:00:00

1 row selected.

SQL>
SQL> --

   
    
    
  








Related examples in the same category

1.Fetch a bulk into a table structure
2.Define a nested table type for each column
3.Extend once, outside the loop for better performance
4.A package to manage a list of employees
5.Fill table of custom type and use it in for loop to insert
6.Table of custome type indexed by BINARY_INTEGER
7.Reference type attribute through index
8.Use for loop to fill a table collection
9.Select bulk collect into
10.Use for loop to insert value to table collection and then use table collection in another insert statement
11.The EXISTS Table Attribute
12.FIRST & LAST Table Attributes
13.NEXT & PRIOR Table Attributes
14.Uses the COUNT method to display the number of rows contained in a table collection
15.How to do a bulk collect into a nested table.
16.How to do a bulk collect into an associative array
17.Table collection indexed by column type