Home » SQL & PL/SQL » SQL & PL/SQL » Issue: Different Date formats in the same cloumn in a table
Issue: Different Date formats in the same cloumn in a table [message #322944] Mon, 26 May 2008 22:34 Go to next message
raghu1040
Messages: 4
Registered: May 2008
Location: PHILY
Junior Member
Hi There,
Pls find below details abt the issue: one of the column 'acct_create_date' in a table contains date format in 2 different ways and stored as varchar2 (datatype).
acct_create_date
format 1) 20041231093030 yyyymmddhhmiss
20041030162525
format 2) 28281230112004 ssmihhddmmyyyy
24241028102004

these r the two different formats a column contains in a table. our objective is to get all the dates to consistent format like yyyymmddhhmiss.
so pls suggest ways...

thanks in advance,
raghu...
raghu1040@gmail.com
Re: Issue: Different Date formats in the same cloumn in a table [message #322945 is a reply to message #322944] Mon, 26 May 2008 22:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Issue: Different Date formats in the same cloumn in a table [message #322998 is a reply to message #322944] Tue, 27 May 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I suggest you convert all date columns into DATE datatype.

Regards
Michel
Re: Issue: Different Date formats in the same cloumn in a table [message #323012 is a reply to message #322944] Tue, 27 May 2008 01:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The problem you have is that you cannot identify the date format of all columns. For some of them, you can tell the format on sight but for others, the format is ambiguous.

MHE
Re: Issue: Different Date formats in the same cloumn in a table [message #323107 is a reply to message #322944] Tue, 27 May 2008 05:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The logic I'd use would be something like this

1) Check the string against the two date masks - if it is only valid with one of them then you know which format it's in, and can convert it to the requird format.

2) If it's valid with both masks, check the year - presumably a year of 2828 is less likely to be valid than a year of 2004.

3) If there's no way to tell, then flag the date for manual checking - I can't think of an automated way to tell which format this date should be in : 20041212122005

4) After getting everything in the correct format, change the column to a Date
Re: Issue: Different Date formats in the same cloumn in a table [message #323137 is a reply to message #323107] Tue, 27 May 2008 07:32 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good logic, could be something like:
SQL> create table t (acct_create_date varchar2(14));

Table created.

SQL> declare
  2    form1 constant varchar2(16) := 'yyyymmddhh24miss';
  3    form2 constant varchar2(16) := 'ssmihh24ddmmyyyy';
  4  begin
  5    for i in 1..10 loop
  6      insert into t 
  7      values (to_char(sysdate-dbms_random.value(0,1000),
  8                      decode(mod(i,2),1,form1,form2)));
  9    end loop;
 10    for i in 1..10 loop
 11      insert into t 
 12      values (trunc(dbms_random.value(20000000,20001000))||
 13               trunc(dbms_random.value(121980,122008)));
 14    end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> select * from t;
ACCT_CREATE_DA
--------------
20060927091915
07041023022006
20070907143554
26291122012006
20050908213917
00120809072007
20051021201027
24561624032008
20080423020510
19300503092007
20000642121983
20000007121990
20000779122002
20000504121980
20000959121998
20000312122005
20000823121983
20000072122001
20000917121995
20000551122005

20 rows selected.

SQL> create or replace type tp is table of varchar2(250);
  2  /

Type created.

SQL> create or replace function f return tp pipelined
  2  is
  3    date1 date;
  4    date2 date;
  5    form1 constant varchar2(16) := 'yyyymmddhh24miss';
  6    form2 constant varchar2(16) := 'ssmihh24ddmmyyyy';
  7    err1 exception;
  8    err2 exception;
  9    err3 exception;
 10    err4 exception;
 11    err5 exception;
 12    err6 exception;
 13    err7 exception;
 14    err8 exception;
 15    err9 exception;
 16    errA exception;
 17    pragma exception_init(err1, -1858); -- Non numeric value
 18    pragma exception_init(err2, -1830); -- Too long value
 19    pragma exception_init(err3, -1840); -- Too short value
 20    pragma exception_init(err4, -1841); -- Invalid year
 21    pragma exception_init(err5, -1843); -- Invalid month
 22    pragma exception_init(err6, -1847); -- Invalid day
 23    pragma exception_init(err7, -1850); -- Invalid hour
 24    pragma exception_init(err8, -1851); -- Invalid minute
 25    pragma exception_init(err9, -1852); -- Invalid second
 26    pragma exception_init(errA, -1839); -- Invalid day for month
 27    
 28  begin
 29    for rec in (select rowid, t.* from t) loop
 30      date1 := null; date2 := null;
 31      begin
 32        date1 := to_date (rec.acct_create_date, form1);
 33      exception
 34        when err1 or err2 or err3 or err4 or err5 or 
 35              err6 or err7 or err8 or err9 or errA
 36          then null;
 37      end;
 38      begin
 39        date2 := to_date (rec.acct_create_date, form2);
 40      exception
 41        when err1 or err2 or err3 or err4 or err5 or 
 42              err6 or err7 or err8 or err9 or errA
 43          then null;
 44      end;
 45      -- Assuming dates must be between 01/01/1980 and sysdate
 46      if not date1 between to_date('01/01/1980','DD/MM/YYYY') and sysdate then
 47        date1 := null;
 48      end if;
 49      if not date2 between to_date('01/01/1980','DD/MM/YYYY') and sysdate then
 50        date2 := null;
 51      end if;
 52      case
 53        when date1 is not null and date2 is not null then
 54          pipe row (rec.rowid||' '||rec.acct_create_date||' both formats are valid');
 55        when date1 is null and date2 is null then
 56          pipe row (rec.rowid||' '||rec.acct_create_date||' both formats are invalid');
 57        else 
 58          pipe row (rec.rowid||' '||rec.acct_create_date||' is '||
 59                    to_char(nvl(date1,date2),'DD/MM/YYYY HH24:MI:SS'));
 60      end case;
 61    end loop;
 62  end;
 63  /

Function created.

SQL> set arraysize 1
SQL> select * from table(f);
COLUMN_VALUE
-------------------------------------------------------------------------------------------
AAAPFpAAEAAACsXAAA 20060927091915 is 27/09/2006 09:19:15
AAAPFpAAEAAACsXAAB 07041023022006 is 23/02/2006 10:04:07
AAAPFpAAEAAACsXAAC 20070907143554 is 07/09/2007 14:35:54
AAAPFpAAEAAACsXAAD 26291122012006 is 22/01/2006 11:29:26
AAAPFpAAEAAACsXAAE 20050908213917 is 08/09/2005 21:39:17
AAAPFpAAEAAACsXAAF 00120809072007 is 09/07/2007 08:12:00
AAAPFpAAEAAACsXAAG 20051021201027 is 21/10/2005 20:10:27
AAAPFpAAEAAACsXAAH 24561624032008 is 24/03/2008 16:56:24
AAAPFpAAEAAACsXAAI 20080423020510 is 23/04/2008 02:05:10
AAAPFpAAEAAACsXAAJ 19300503092007 is 03/09/2007 05:30:19
AAAPFpAAEAAACsXAAK 20000642121983 both formats are invalid
AAAPFpAAEAAACsXAAL 20000007121990 is 07/12/1990 00:00:20
AAAPFpAAEAAACsXAAM 20000779122002 both formats are invalid
AAAPFpAAEAAACsXAAN 20000504121980 is 04/12/1980 05:00:20
AAAPFpAAEAAACsXAAO 20000959121998 both formats are invalid
AAAPFpAAEAAACsXAAP 20000312122005 both formats are valid
AAAPFpAAEAAACsXAAQ 20000823121983 is 23/12/1983 08:00:20
AAAPFpAAEAAACsXAAR 20000072122001 both formats are invalid
AAAPFpAAEAAACsXAAS 20000917121995 is 17/12/1995 09:00:20
AAAPFpAAEAAACsXAAT 20000551122005 both formats are invalid

20 rows selected.

Regards
Michel
Previous Topic: Issue while using Bulk Collect
Next Topic: UTL_FILE
Goto Forum:
  


Current Time: Sun Dec 04 14:27:31 CST 2016

Total time taken to generate the page: 0.14652 seconds