CODEWITHSUNDEEP
oracle-databasedate
Dharmaraj
Dharmaraj

Reputation: 1

Want to convert Mutiple Date format into this format "yyyy-mm-dd" in oracle

User give any date format value ,

Select To_Date('20-11-2014','yyyy-mm-dd')  From Dual ;

(or)

Select To_Date('2014-11-28','yyyy-mm-dd')  From Dual ;

But we want to convert into single format(this format)

select to_char(to_date(trunc(datereceipt), 'DD-MM-YYYY'), 'yyyy-mm-dd') from vttrcwfheggrecivdlot

datereceipt := '20-11-2014';

(this format is ok for above query)

but 

datereceipt := '2014-11-28';

(For using this format want to convert the date format in datediff function all operation are performing in runtime)

Upvotes: 0

Views: 575

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132580

You may need to accept date inputs (strings) in different formats, but you just need to convert them to dates, not to a formatted string. A function like this can be used:

function my_to_date (p_str varchar2) return date
is
begin
   return to_date (p_str, 'DD/MM/YYYY');
exception
   when others then
      return to_date (p_str, 'YYYY-MM-DD');
end;

Test:

SQL> alter session set nls_date_format = 'YYYY-MM-DD';

Session altered.

SQL> declare
  2     function my_to_date (p_str varchar2) return date
  3     is
  4     begin
  5        return to_date (p_str, 'DD/MM/YYYY');
  6     exception
  7        when others then
  8           return to_date (p_str, 'YYYY-MM-DD');
  9     end;
 10  begin
 11     dbms_output.put_line(my_to_date('29-DEC-1999'));
 12     dbms_output.put_line(my_to_date('30/12/1999'));
 13     dbms_output.put_line(my_to_date('1999-12-31'));
 14  end;
 15  /
1999-12-29
1999-12-30
1999-12-31

PL/SQL procedure successfully completed.

Upvotes: 0

a.j. tawleed
a.j. tawleed

Reputation: 904

If there are just this two date format, this works:

select decode(instr(:datereceipt, '-'), 3,   to_char(to_date(:datereceipt, 'DD-MM-YYYY'), 'yyyy-mm-dd'), :datereceipt) 
from dual

or if you want them as dates

select decode(instr(:datereceipt, '-'), 3,  to_date(:datereceipt, 'DD-MM-YYYY'), to_date(:datereceipt, 'yyyy-mm-dd')) 
from dual

Upvotes: 1

Related Questions