|
Re: Different output in forms and pl sql [message #539233 is a reply to message #539232] |
Thu, 12 January 2012 06:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
It's probably because you are using to_date on a date. Sysdate is a date, I imagine dob is as well. Doing so is a bug.
to_date does not accept a date parameter. So oracle implicitly converts the date to a varchar using your sessions date format.
i.e.
is really
to_date(to_char(sysdate, <nls_date_format>), nls_date_format)
Try getting rid of the to_dates.
|
|
|
|
|
Re: Different output in forms and pl sql [message #539279 is a reply to message #539256] |
Thu, 12 January 2012 21:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/169590.jpg) |
Tomcrzy
Messages: 101 Registered: December 2011 Location: Chennai,India
|
Senior Member |
![romogenia@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
Yes now it is giving correct output.
I thought that before to_date()-to_date() only can give the output.
Oh ...it has really gave me some bugs only.
This is the one lead to me the correct way.
SELECT to_date(TO_CHAR(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY')-
to_date(TO_CHAR(dob,'DD/MM/YYYY'),'DD/MM/YYYY')
INTO v_sub
FROM customer_details
WHERE application_id=p_id;
v_dobcalc :=ROUND(v_sub/366);
But what is that -73 indicate? is it a ASCII value which we get as a result of the subtraction.
Thank u.
[Updated on: Thu, 12 January 2012 21:45] Report message to a moderator
|
|
|
|
Re: Different output in forms and pl sql [message #539293 is a reply to message #539279] |
Fri, 13 January 2012 01:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Out of topic: your solution just reminded me this comic (although you have still some place to improve): http://xkcd.com/763/
Tomcrzy wrote on Fri, 13 January 2012 04:39But what is that -73 indicate? is it a ASCII value which we get as a result of the subtraction.
As the value is probably supposed to be date difference in years (by the way incorrect - although year 2012 has 366 days, most other ones do not) and 27-(-73)=100, this is just a side effect of having two digit year (most probably 'YY') in NLS_DATE_FORMAT mask. So, DOB is converted to e.g. string DD-MM-85, which is supposed to be in year 2085 and not 1985. Anyway, when you avoid implicit conversion, NLS_DATE_FORMAT will not be used and it will be processed correctly.
To the difference count: subtraction gives the difference in days; for getting years, use MONTHS_BETWEEN function and divide it by 12 (fortunately, all years have 12 months).
|
|
|
|
Re: Different output in forms and pl sql [message #539305 is a reply to message #539294] |
Fri, 13 January 2012 04:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I tell you that using to_Date on a date is a bug. I explain that it's a bug because to_date doesn't accept a date parameter and oracle has to implicitly to_char it first.
I then explicitly tell you to remove the to_date.
You instead add the implicit to_char explicitly.
You really didn't read my post very carefully did you?
Don't change datatypes if you don't need to.
|
|
|
Re: Different output in forms and pl sql [message #539306 is a reply to message #539293] |
Fri, 13 January 2012 04:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
flyboy wrote on Fri, 13 January 2012 07:38To the difference count: subtraction gives the difference in days; for getting years, use MONTHS_BETWEEN function and divide it by 12 (fortunately, all years have 12 months).
The difference in the counts will have been due to different nls_date_formats being used by sqlplus and forms. Forms will have been using YY and sqlplus YYYY.
I agree that months_between is the best way to do this.
|
|
|
|