Home » SQL & PL/SQL » SQL & PL/SQL » Date
Date [message #291057] Wed, 02 January 2008 22:07 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Dear Friends,

Assume my coding would like this

create or replace procedure proc1(d1 date)
as
begin
dbms_output.put_line(d1);
end;


If i call this proceudre in any function or another procedure

call proc1(d1);

This d1 parameters is whatever date format, for example

20-JAN-1983
20-01-1983
20-01-83
20-JAN-83

What i need means, if i call this procedure in anywhere i am sending like that parameter, i want to execute that procedure that means that date should be printed out.

Anybody came across like this scenorio

Please find a solution and post that.

Thanks & Regards

Mano



Re: Date [message #291058 is a reply to message #291057] Wed, 02 January 2008 22:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You have to pass the date and not the varchar2 as arguement.

To make it more clear , You have to use TO_DATE function to convert Character to date.

ie, '20-JAN-1983' or '20-01-1983' are Characters and not dates.

Thumbs Up
Rajuvan.

[Updated on: Wed, 02 January 2008 22:21]

Report message to a moderator

Re: Date [message #291059 is a reply to message #291057] Wed, 02 January 2008 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You may get your questions answered faster if you would read & FOLLOW the posting guidelines as stated above.

If you choose to NOT follow the guidelines, then we can choose to ignore your posts.
Re: Date [message #291061 is a reply to message #291057] Wed, 02 January 2008 22:26 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Sorry friend.

i have used to_date function.

If i used to_date(d1,'dd-mon-yy') means

it won't accept 20-01-2007 and 20-01-07
dates

it accept only 20-JAN-07 and 20-JAN-2007 dates
only.

I need, it should be accept both. How we can do?

any way from you?

Thanks
Mano






Re: Date [message #291063 is a reply to message #291057] Wed, 02 January 2008 22:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It appears you do NOT really understand the basics of Oracle data types.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm
You're On Your Own (YOYO)!
Re: Date [message #291064 is a reply to message #291057] Wed, 02 January 2008 22:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

But for any 'genuine' application , it would be keeping some sort of uniformity in term of its attributes.

How are you getting all these 'dates formats' to pass ? If you are getting like that it is a flaw in your application.

Thumbs Up
Rajuvan.
Re: Date [message #291065 is a reply to message #291057] Wed, 02 January 2008 22:44 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
So,
Can't we send different date format
as a parameter?

The customer Can type any date format right?

If any solution welcomes.

Thanks

Mano
Re: Date [message #291067 is a reply to message #291057] Wed, 02 January 2008 22:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
what date is '07-06-08'?

[Updated on: Wed, 02 January 2008 22:49] by Moderator

Report message to a moderator

Re: Date [message #291068 is a reply to message #291057] Wed, 02 January 2008 22:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There cannot be different date format. There is only One 'DATE' format in Oracle.

But what you are talking about is chanracter formats .

Thumbs Up
Rajuvan.

[Updated on: Wed, 02 January 2008 22:50]

Report message to a moderator

Re: Date [message #291070 is a reply to message #291057] Wed, 02 January 2008 22:57 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
QUOTE]
create or replace procedure proc1(d1 date)
as
begin
dbms_output.put_line(d1);
end;
[QUOTE]

Please see the above simple procedure.
Assume this is public procedure all users.

users have to send date as a parameter from

Textbox value or any other
procedure or any other function
from call that procedure.. that
time they are sending different
date format mm-dd-yy, mm-dd-yyyy,
mm/dd/yy, mm/dd/yyyy, dd-mon-yy...

This time the above procedure accept or not?

or

We can send dd-mon-yy format only?


Thanks

Mano
Re: Date [message #291071 is a reply to message #291057] Wed, 02 January 2008 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
NO!
Re: Date [message #291076 is a reply to message #291057] Wed, 02 January 2008 23:20 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

To finish this posting off ,

Oracle doesn't care what character format you are using before converting it into date. It only needs to get actual 'DATE' datatypes at the input side.

Quote:

We can send dd-mon-yy format only?


It doesn't mean that you can use only 'dd-mon-yy' , but whatever character format you are using , you should convert into exact 'DATE' in Oracle.

Like Ana asked , can you describe the date string of '07-06-08' ?

is it '07-AUG-2008' ?
Or '08-JUL-2008'
Or '06-AUG-2007' Or ... ?

If we were not able to detect exactly , On passing such string , how can expect Oracle to describe such kind of strings ?

Thumbs Up
Rajuvan.
Re: Date [message #291078 is a reply to message #291057] Wed, 02 January 2008 23:24 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member

i got sol from simple explantion from Rajuvan.

any how thanks.

i understood, no way to detect exact date.
we have to send a correct date parameter,
otherwise we have to check it out from
programming codes.

Thanks

Mano






Re: Date [message #291111 is a reply to message #291078] Thu, 03 January 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a trick.
If you use only digits in your format model when converting into date, Oracle is smart enough to understand month name (in your current data language) and it does not care of the separator you give:
SQL> with data as (
  2  select '20-JAN-1983' dat from dual union all
  3  select '20-01-1983' dat from dual union all
  4  select '20-01-83' dat from dual union all
  5  select '20-JAN-83' dat from dual)
  6  select to_char(to_date(dat,'DD/MM/YYYY'),'DD-MON-YYYY') 
  7  from data
  8  /
TO_CHAR(TO_
-----------
20-JAN-1983
20-JAN-1983
20-JAN-0083
20-JAN-0083

4 rows selected.

Regards
Michel
Re: Date [message #291259 is a reply to message #291111] Thu, 03 January 2008 09:45 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Oracle is not "smart enough" to determine your date field and it does care what separator you use. When you're using character strings that look like dates, you need to convert them to Oracle's data format.

Sorry friend. 
i have used to_date function. 
If i used to_date(d1,'dd-mon-yy') means
it won't accept 20-01-2007 and 20-01-07 dates
it accept only 20-JAN-07 and 20-JAN-2007 dates
only.


The original problem is that the format statement in the to_date function is not correct for the string that's been entered. Go google for "Oracle date formats" and see what comes up.

to_date('01-JAN-2008','DD-MON-YYYY')
to_date('12/31/2007','MM/DD/YYYY')
to_date('09/24/08','MM/DD/RR')

to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')


HTH,
Ron

P.S. Using the format 'dd-mon-yy' to convert the string '20-01-07' is going to translate the date to 20-jan-0007, which I don't think you want!
Re: Date [message #291260 is a reply to message #291259] Thu, 03 January 2008 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Oracle is not "smart enough" to determine your date field and it does care what separator you use

So how do you explain the example I gave?

Regards
Michel
Re: Date [message #291273 is a reply to message #291260] Thu, 03 January 2008 10:14 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Well, now you've got me stumped on that one. I've always received date format errors when I've tried to enter a date string with a format different from the format listed in the to_date function. I'll have to look into this further as it would be a big help in a lot of the web forms I create that need a date input.

Is there some documentation on line that explains why Oracle would accept a date string in a format different than the one specified? I'd like to study this more.

Ron
Re: Date [message #291280 is a reply to message #291273] Thu, 03 January 2008 10:53 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not explicitly said but the opposite is said a long with the FX format model modifier.
Quote:

FX
Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function:

Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.

The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.

Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes.

Maybe searching on these FX format model modifier you will get more information, especially on Metalink, I didn't check.

Regards
Michel

[Updated on: Thu, 03 January 2008 10:53]

Report message to a moderator

Previous Topic: ORA-01031: insufficient privileges
Next Topic: Pl/Sql Procedure
Goto Forum:
  


Current Time: Thu Dec 08 02:23:29 CST 2016

Total time taken to generate the page: 0.27483 seconds