| date [message #36040] |
Wed, 31 October 2001 10:14  |
Chris
Messages: 128
Registered: November 1998
|
Senior Member |
|
|
Hi,
iam getting a input as date in char.
i have check whether the input passed
is in this MM-DD-YY format.
if not i have convert that, i think i know how to convert it, but how to check.
Thanks in advance.
----------------------------------------------------------------------
|
|
|
|
| Re: date [message #36041 is a reply to message #36040] |
Wed, 31 October 2001 13:49  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Beware of yy and rr. rr is probably better for your purposes:
select 'yy 99', to_char( to_date( '99', 'yy' ), 'yyyy' ) from dual
union all
select 'rr 99', to_char( to_date( '99', 'rr' ), 'yyyy' ) from dual
union all
select 'yy 50', to_char( to_date( '50', 'yy' ), 'yyyy' ) from dual
union all
select 'rr 50', to_char( to_date( '50', 'rr' ), 'yyyy' ) from dual
union all
select 'yy 49', to_char( to_date( '49', 'yy' ), 'yyyy' ) from dual
union all
select 'rr 49', to_char( to_date( '49', 'rr' ), 'yyyy' ) from dual
union all
select 'yy 01', to_char( to_date( '1', 'yy' ), 'yyyy' ) from dual
union all
select 'rr 01', to_char( to_date( '1', 'rr' ), 'yyyy' ) from dual;
yy 99 2099
rr 99 1999
yy 50 2050
rr 50 1950
yy 49 2049
rr 49 2049
yy 01 2001
rr 01 2001
see this convertor code which tries each format until it finds a matching format. obviously it can't distinguish between mm-dd and dd-mm.
-- From http://www.revealnet.com/Pipelines/PLSQL/archives.htm#code15
create or replace PACKAGE dt
IS
/* The replacement for TO_DATE */
FUNCTION val (value_in IN VARCHAR2) RETURN DATE;
END dt;
/
create or replace PACKAGE BODY dt
IS
/*
|| Declare the structure of the PL/SQL table which will hold
|| the masks. Then declare the table itself.
*/
TYPE mask_tabtype IS TABLE OF VARCHAR2 (30) INDEX BY BINARY_INTEGER;
fmts mask_tabtype;
fmt_count INTEGER;
FUNCTION val (value_in IN VARCHAR2) RETURN DATE
IS
retval DATE := NULL;
/* Loop index for the scan through the masks */
mask_index INTEGER := 1;
/* Boolean to terminate loop if date was converted */
date_converted BOOLEAN := FALSE;
BEGIN
/* Convert from masks in table */
IF value_in IS NULL
THEN
date_converted := TRUE;
ELSE
/* Loop through the rows in the table... */
WHILE mask_index <= fmt_count AND NOT date_converted
LOOP
BEGIN
/* Try to convert string using mask in table row */
retval := TO_DATE (value_in, fmts (mask_index));
p.l ('Converting with ' || fmts (mask_index));
date_converted := TRUE;
EXCEPTION
WHEN OTHERS
THEN
retval := NULL;
mask_index:= mask_index+ 1;
END;
END LOOP;
END IF;
IF date_converted
THEN
RETURN retval;
ELSE
RAISE VALUE_ERROR;
END IF;
END val;
BEGIN
/* ------------ Initialization Section of Package ------------*/
fmts(1) := 'DD-MON-RR';
fmts(2) := 'DD-MON-YYYY';
fmts(3) := 'DD-MON';
fmts(4) := 'MM/DD';
fmts(5) := 'MM/RR';
fmts(6) := 'MMDDRR';
fmts(7) := 'MM/YYYY';
fmts(8) := 'MM/DD/RR';
fmts(9) := 'MM/DD/YYYY';
fmts(10) := 'MMDDYYYY';
fmts(11) := 'YYYYMMDD';
fmts(12) := 'RRMMDD';
fmt_count := 12;
END dt;
/
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
