Re: Not a valid month error

Jim Kennedy (kennedy-downwithspammersfamily_at_attbi.net) wrote:

: "Karuna" <mrinalini.sukumar_at_gmail.com> wrote in message
: news:1112220761.905417.48690_at_z14g2000cwz.googlegroups.com...
: >
: > Dave wrote:
: > > "Karuna" <mrinalini.sukumar_at_gmail.com> wrote in message
: > > news:1112209818.353192.8700_at_f14g2000cwb.googlegroups.com...
: > > >
: > this error in this situation?
: > > >>
: > > select to_date(orderDate,'mmddyy') from order; !!!!!!!!
: > >
: > > gee whiz
: >
: > Well I had tried that as well and it's not working
: >
: Real easy. Change the column from a character to a date. You shouldn't
: store "dates" in a column that holds strings. Whomever came up with that
: design doesn't know the basics. Would you store characters in a numeric
: column as ASCII numbers? Of course not.

: Write a pl/sql function that converts a string to a date except when it gets
: an error, then return the string and the word 'ERROR!!!' then do

: select orderDate from order where myFunction(orderDate)='ERROR!!';

: eg (not compiled or tested)

: create or replace myFunction(v_date in varchar2) return varchar2 is
: begin
: return to_char(to_date(v_date,'mmddyy');
: exception when others then
: return 'ERROR!!';
: end;
: /

: But change the damn table and fire the fool who designed it!
: Jim

You missed the part where he (she?) said

	"I actually am doing my assignment and the table is created my
	instructor and I have to use the table. I can't change the table
	definition. I am using UNIX and don't know sql*plus vesrion.
	"

I suspect the point is to learn about a variety of issues - and they certainly are!

--

This space not for rent.