| Reg:Date validation in SQL............ [message #8479] |
Mon, 25 August 2003 02:56  |
BABU SRSB
Messages: 42
Registered: June 2002
|
Member |
|
|
Hi All,
I have the data is coming from some different source.
The field is varchar of 12 length. The data should follow the DDMMYYHH24MISS format. and later i have to convert this field to date and need the hours difference to sysdate.
The sample data is....
date1
-------------------
250803113723
250803113608
250803122826
250803200812
All above are satisfying the 'DDMMYYHH24MISS' format. If this is the data, i can easily achieve my target. But some time the data is not following the 'DDMMYYHH24MISS' format.
It's coming like that.
date1
-------------------
250803113723
250803113608
250803122826
250803200812
250803312719 <-- incorrect format in hours field.
250803026919 <-- incorrect format in minutes field.
250803021969 <-- incorrect format in seconds field.
252903026919 <-- incorrect format in months field.
If i am applying to_date function, simply throwing oracle error.
So, i want to filter those incorrect format fields. then i will consider the valid format things only to calculate the hours difference.
can any function to check that type of data is date or not.
Immediate response is appreciatble.
Regards,
Babu.
|
|
|
|
| You can create a function .... [message #8481 is a reply to message #8479] |
Mon, 25 August 2003 03:08  |
aix_tom
Messages: 4
Registered: August 2003
|
Junior Member |
|
|
Somewhat like this :
create or replace function save_to_date (datechar in varchar) return date is
tempdate date;
begin
tempdate := to_date(datechar, 'DDMMYYHH24MISS');
return tempdate;
exception
when others then
return to_date('010100000000','DDMMYYHH24MISS');
end;
When the date conversion is successfull the date is returned, if it's not successfull january 1st 2000 is returned.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
