Issue: Different Date formats in the same cloumn in a table [message #322944] |
Mon, 26 May 2008 22:34  |
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 #323107 is a reply to message #322944] |
Tue, 27 May 2008 05:41   |
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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|