CODEWITHSUNDEEP
sqlplsql
Orion
Orion

Reputation: 13

Identify date format in PLSQL

I'm trying to write a plsql stored procedure which identifies any date format and converts it into single datetime format 'mm/dd/yyyy hh:mi:ss'. How do I do it. I tried using case statment but there are so many date combinations that its not possible to write case statment for all of them. For ex: 27-Oct-1967, October 27 1967 11:15:45, 1967-OCT-27, etc. How do I convert all these to single format. Thank you.

Upvotes: 1

Views: 1121

Answers (3)

Art
Art

Reputation: 5792

Maybe this can help you:

CREATE TABLE temp_date
AS
SELECT '1967-OCT-27' some_date 
FROM dual
UNION
SELECT '27-Oct-1967' FROM dual
UNION
SELECT 'October 27 1967 11:15:45' FROM dual
/

Declare
  CURSOR i_cur IS
  Select some_date 
   From temp_date;
  --
  v_date1 Varchar2(30);
  v_date2 Varchar2(30);
  v_date3 Varchar2(30);
  v_char  Varchar2(30);
  v_cnt   Number:= 0;
Begin
  For i_rec IN i_cur
  Loop
    v_cnt:= v_cnt + 1;
    Begin
      v_date1:= to_char(to_date(i_rec.some_date), 'MM/DD/YYYY hh24:mi:ss');
      dbms_output.put_line(v_cnt||'.'||chr(9)||v_date1);
      EXCEPTION
        When Others Then
          Begin
            v_date2:= to_char(to_date(i_rec.some_date, 'MM/DD/YYYY hh24:mi:ss'), 'MM/DD/YYYY hh24:mi:ss');
            dbms_output.put_line(v_cnt||'.'||chr(9)||v_date2);
            EXCEPTION
              When Others Then 
                Begin
                 v_date3:= to_char(to_date(i_rec.some_date, 'YYYY-MON-DD'), 'MM/DD/YYYY hh24:mi:ss');
                 dbms_output.put_line(v_cnt||'.'||chr(9)||v_date3);
                 EXCEPTION
                   When Others Then 
                   -- Insert into Exceptions table (or any temp table) then convert... --
                    v_char:= i_rec.some_date;
                    dbms_output.put_line(v_cnt||'. '||chr(9)||i_rec.some_date||' : '||v_char);
                End;
          End;
    End;
  End Loop;
End;
/

1.  10/27/1967 00:00:00
2.  10/27/1967 00:00:00
3.  10/27/1967 11:15:45

Upvotes: 0

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10073

Simply to_char() will do,

select to_char(yourDateField,'mm/dd/yyyy hh:mi:ss') from dual;

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

The most desired way of would be to formatting date into the format that you need. Then do whatever calculations that you require on it.

Otherwise you will have to write ridiculously large number of select cases to define the format. Not to say that it doesn't make sense since dates can come in many different formats....as Mat mentioned. And further Date is a component that can be influenced by your system.

You may try the following:

Convert date input into the desired format using To_Date() given you may not even know if this input comes as a String or a real date. So you may need some validations to make sure it's a proper date.

SELECT TO_DATE(mydate,'mm/dd/yyyy hh:mi:ss') FROM Dual;

Upvotes: 0

Related Questions