Function problem with varchar2 to date

[kokiri]

I have a field that collects visit date & time but it is stored in varchar2 format 'hh24mi ddMonYYYY' and I thought this function will solve the problem but it doesn't.

create or replace function getfulldt(x in varchar2)
return varchar2 is
y Date;
begin
if x is
y := to_date(x, 'hh24mi dd-Mon-YYYY') ;
return ;
exception
when others then
return null;
end;
/

This function only truncates to date and do not display hours and minutes.

How can I fix so that it will display in date format with hours and minutes?

Thanks for your help in advance.

Di

 

[BJCooperIT]

The date returned by the function needs to be formatted in order to display the elements you want.
Try something like:

to_char(getfulldt(x),'mm/dd/yyyy hh24:mi:ss')

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[kokiri]

Hi BJCooperIT,

Thank you for your information but I'm getting 'ORA-01722 Invalid Number' error.

FYI, some folks are hand jamming the data and data is not consistent.

Thanks.

 

[SantaMufasa]

Kokiri,

The First Rule of Data Quality is, "Let bad data live as short a life as possible." If your people are able to successfully enter "bad date" values into a character (not DATE) field, that is not good. Rather than coming up with myriad after-the-fact remedies to deal with lousy DATE data stored in VARCHAR2 fields, you should change the receiving field to Oracle DATE data type. That is a relatively small maintenance activity.

There cannot be a good business or technical justification for storing DATE data in non-DATE columns, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Beilstwh]

I agree with Dave 100%. In my many years of programming, one of the biggest problems with other programmers code has always been date handling. NEVER store a date value as a varchar2 in Oracle. There is NO valid reason to do so. Stop the user from entering an invalid date at the source and store it as date. If they have to enter a time element then include it with the date field mask or have a seperate field for time that is of type date and that will use a format to validate the time entered and then merge the date and time fields. What you are doing is unclean and will (and has) caused you problems.

Bill
Oracle DBA/Developer
New York State, USA

 

[kokiri]

Hi Santa & Bill,

I totally agree with you guys. I wish I have control over when we collect the data. Unfortunately, I don't have that option. We are using proprietary product and ALL of data elements are in varchar2 format. Bottom line is that I just have to deal with it.

Thanks.

 

[SantaMufasa]

Kokiri,

Sorry to say, but your vendors of the proprietary product are HACKS. Reputable vendors don't distribute a product that doesn't have proper editing and enforcement of quality data.

With new Rapid-Application-Development products that are now available and coming on the market, where companies can create new, tailored replacement applications in a matter of days/weeks instead of months/years for less cost than licensing HACK applications from vendors, the HACKS will soon find themselves out of business.

You have the right to expect/demand at least minimal quality from your applications. I'd threaten a law suit for something like your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[kokiri]

Sata,

Thanks. I questioned these problems in the past about data quality issue but so far no luck. Majority of users don't deal with the data quality as long as it is documented in the system. I hope the vendor will make changes to the product soon but I'm not counting on it. I wish I can threaten a law suit.

Thanks.

 

[Thargy]

Kokiri,

everyone else beat me to the punch with their correct assertion that storing dates as anything other than a DATE data type is asking for disaster. Your real problem appears to be an unwillingness (or inability) to validate date fields on the part of your front-end supplier.

I suggest the following tactics as possible avenues of 'attack'.

Oracle corporation has invested mega bucks in its date handling, just like Microsoft and Sun. By not using DATE data types, all that free effort is wasted. Can your company really afford to duplicate that amount of effort?

Second, put validation on the offending data sources and keep on rejecting the data. You can dress this up as "we've just enhanced our data integrity checking and we note that your application is consistently and verifiably producing faulty data, blah blah blah." This puts so much egg on the offending punter's face that they usually back down.

Third, I never ever allow a user to enter a date, it is always via selection from a date control. That way the control guarantees rigour, at source. If users really want loads of extra key strokes, get them to justify wasting all that time. Text based date entry can sometimes be justified, but not often.

Enough already, but as a serious guy, and not a hacker, don't ever knowingly allow crud into your database, remember GIGO!

Regards (and sympathy)

Tharg

Grinding away at things Oracular

 

[BJCooperIT]

Procedures and theory are all well and good, but consider the poor developer who sits at the receiving end of someone else's decisions. Many times I have been in the position of being told to "just handle it". Perhaps it is legacy data that must be converted, perhaps data that we cannot control on the front end. Sometimes we developers just have to get the job done.

I agree that storing dates in text fields is a terrible practice, but we do kokiri a disservice by not also offering a solution to his/her problem. Dave, have you forgotten your rather elegant solution to try and handle this problem? See: thread186-690660. It may not handle all permutations, but it is a good start.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[Beilstwh]

I have a suggestion, can you create triggers? If you can, then you could use a version of Santa's routine to sanitize the data before it is stored in the database. It would still be in varchar2 (yuck!!!), but would be in a standard format so that your routines could reference the data.

Bill
Oracle DBA/Developer
New York State, USA

 

[kokiri]

Hi all,

Yes. BJCooperIt is correct. We cannot control on the front end. I just have to deal with it. I requested modification of application, but it may take years.

Bill--I don't have permission to create triggers. FYI, I don't have permission to access tables. I'm only allow to access views.

Thanks.

 

[SantaMufasa]

Barb, I don't know about you, but I never forget code I've written...I sometimes even dream my code at night. <grin>

Yes, I could certainly come up with "The Mother of All Date-Validation Functions", but as Tharg so aptly pointed out, Oracle (et. al.) have invested tons of money already, so we should take advantage (for no additional cost on our parts).

The objective of my post, above, (as is usually the case when I get up on my soap box) is to encourage Kokiri to implement the correct solution rather than to build tons of infrastructure to make a burlap purse out of a pig's ear (when s/he could have silk for little/no cost). Putting up with crap from vendors propagates CRAP FROM VENDORS. It is unacceptable (IMHO) to sit and "take it"...it is tantamount to buying a new car, without a warranty, and having the windows and doors not close or open properly.

So, yes, Kokiri, I invite you to use some variant of functions that we have already posted here on Tek-Tips, but in repayment for your using those functions, I expect you to contact the vendor and let them know that their not using data types for the purpose they were intended is malfeasance, ridiculous, and unacceptable, and that you can't wait to broadcast negative references regarding their software.

Here is your first opportunity: Who is the Vendor, Where are they from, and What is the application (name)?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Turkbear]

Hi,
To go back to your original posting..If the input format is:
varchar2 format 'hh24mi ddMonYYYY'


In your function you seem to be checking for the wrong format:
y := to_date(x, 'hh24mi dd-Mon-YYYY') ;


Is that a typo? Try it as
y := to_date(x, 'hh24mi ddMonYYYY')

Example (Using the conversion to the desired display format posted by BJCooperIT):

select to_char(to_date('2212 12OCT2005','hh24mi ddMONYYYY'),'mm/dd/yyyy hh24:mi:ss') dt from dual

Returns:
DT
-------------------
10/12/2005 22:12:00

Hope it may help show a possible way to go..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BJCooperIT]

Whew! Feel better now?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[kokiri]

Hi DT,

Thanks but I already tried that option and results are still same.

select data, to_date(getfulldt(data)) visitdate
from test
-----
2333 17Nov2005 17-Nov-2005 12:00:00 AM

Thanks.

 

[kokiri]

Santa,

My vendor is using "Oracle" as their sales pitch. All of
their application is developed in some legacy product and transfer the data to Oracle.

Bottom line is that we made enough complaints but so far no luck. I just have to deal with it.

 

[Turkbear]

Hi, I must have missed something:
In your test of the code I posted did you change your function and then use the to_char function to format the display output?
Given:
select data, to_char(getfulldt(data),'mm/dd/yyyy hh24:mi:ss') visitdate
from test

If
y := to_date(x, 'hh24mi ddMonYYYY')
and
the data supplied to the Function is actually in this format (2212 12OCT2005)
and
the Function returns y as a Date,
Then
To_Char(y,'mm/dd/yyyy hh24:mi:ss') would display
10/12/2005 22:12:00

Am I missing something in your process?








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kokiri]

Hi Turkbear,

In getfulldt function, I tested both format but results are same. It truncates and do not display hours and minutes.
y := to_date(x, 'hh24mi ddMonYYYY') and
y := to_date(x, 'hh24mi dd/Mon/YYYY')

See below sample data and when I tested with your suggestion and I received an error. (ORA-01722: invalid number)

SQL> select data, getfulldt(data) visitdate
2 from test;
DATA VISITDATE
-------------------- --------------------
1551 17Nov2005 17-NOV-05
1626 19Nov2005 19-NOV-05
1103 20Nov2005 20-NOV-05
1626 19Nov2005 19-NOV-05
1109 20Nov2005 20-NOV-05
1555 17Nov2005 17-NOV-05
1618 18Nov2005 18-NOV-05

18Nov2005
2333 17Nov2005 17-NOV-05....

SQL> select to_char(data,'mm/dd/yyyy hh24:mi') visitdate
2 from test;
select to_char(data,'mm/dd/yyyy hh24:mi') visitdate
*
ERROR at line 1:
ORA-01722: invalid number

select to_char(getfulldt(data),'mm/dd/yyyy hh24:mi') visitdate
*
ERROR at line 1:
ORA-01722: invalid number

Thanks.

 

[BJCooperIT]

You need to return a DATE from your function.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus