Home » SQL & PL/SQL » SQL & PL/SQL » issue with to_date and to_char
issue with to_date and to_char [message #263977] Fri, 31 August 2007 07:15 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
I have a procedure, which has a cursor, based on which
i am generating a report..there is a date column, which
should appear as MM/DD/YYYY format, and i am getting the
min and max dates..for the purpose of reporting

i am giving an example table here, original table is something
else


create table gt1(mydate date);
insert into gt1 values(to_date('08/07/2007','MM/DD/YYYY'));
insert into gt1 values(to_date('08/06/2007','MM/DD/YYYY'));
insert into gt1 values(to_date('08/06/2006','MM/DD/YYYY'));
insert into gt1 values(to_date('08/09/2007','MM/DD/YYYY'));
insert into gt1 values(to_date('08/16/2006','MM/DD/YYYY'));




SQL> SELECT * FROM GT1 ORDER BY MYDATE;

MYDATE
---------
06-AUG-06
16-AUG-06
06-AUG-07
07-AUG-07
09-AUG-07


i have the query in the cursor like this

SQL> SELECT MIN (TO_DATE(TO_CHAR(MYDATE,'MM/DD/YYYY'),'MM/DD/YYYY')) min_issue_d,
  2             MAX(TO_DATE(TO_CHAR(MYDATE,'MM/DD/YYYY'),'MM/DD/YYYY')) max_issue_d FROM GT1;

MIN_ISSUE MAX_ISSUE
--------- ---------
06-AUG-06 09-AUG-07


which gives me the min and max dates alright, but i want to
see it in MM/DD/YYYY format, the problem is:

Earlier there was no to_date in the query, but the customer
complaining that the order is jumbled (min, and max not getting correct results)..so i included to_date
so that i get the exact min and max dates..but now i want to
see it in MM/DD/YYYY format..when i issue above query,
i am getting it in another format..

any suggestions?





Re: issue with to_date and to_char [message #263980 is a reply to message #263977] Fri, 31 August 2007 07:22 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use to_char with the date format on top of MIN and MAX.

By
Vamsi
Re: issue with to_date and to_char [message #263981 is a reply to message #263977] Fri, 31 August 2007 07:22 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I don't see ant problems

SQL> select min(mydate) mindate, max(mydate) from gt1;

MINDATE   MAX(MYDAT
--------- ---------
06-AUG-06 09-AUG-07

SQL> select to_char(min(mydate),'MM/DD/yyyy') mindate, to_char(max(mydate),'MM/DD/YYYY') from gt1;

MINDATE    TO_CHAR(MA
---------- ----------
08/06/2006 08/09/2007
Re: issue with to_date and to_char [message #263997 is a reply to message #263977] Fri, 31 August 2007 08:00 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
soujanya_srk wrote on Fri, 31 August 2007 08:15

SQL> SELECT MIN (TO_DATE(TO_CHAR(MYDATE,'MM/DD/YYYY'),'MM/DD/YYYY')) min_issue_d,
  2             MAX(TO_DATE(TO_CHAR(MYDATE,'MM/DD/YYYY'),'MM/DD/YYYY')) max_issue_d FROM GT1;

MIN_ISSUE MAX_ISSUE
--------- ---------
06-AUG-06 09-AUG-07




MYDATE is already a DATE, so you don't need to unnecessary TO_DATE. A simple TO_CHAR is all you need as others have shown. Using TO_DATE on a DATE column can only cause problems, as you can see.
Re: issue with to_date and to_char [message #264126 is a reply to message #263977] Fri, 31 August 2007 23:59 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Simply use to_char function, need not use to_date function

select to_char(max(MYDATE),'MM/DD/YYYY') min_issue_d,
to_char(min(MYDATE),'MM/DD/YYYY') max_issue_d
from GT1
Re: issue with to_date and to_char [message #264130 is a reply to message #264126] Sat, 01 September 2007 00:28 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, read the other posts.
It is useless too repeat them.

Regards
Michel
Previous Topic: BUFFER OVERFLOW PROBLEM
Next Topic: Month from JAN to DEC and not from APR to SEP
Goto Forum:
  


Current Time: Sun Dec 04 20:54:19 CST 2016

Total time taken to generate the page: 0.20052 seconds