Home » SQL & PL/SQL » SQL & PL/SQL » NEAREST and DATE (yet again merged)
NEAREST and DATE (yet again merged) [message #225923] Wed, 21 March 2007 23:46 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
how to find the data which are NEAREST TO SYSDATE ORACLE 9I
like tablename bill columnname review_date
find NEAREST TO SYSDATE orderby review_date
thanxs


[Updated on: Wed, 21 March 2007 23:46]

Report message to a moderator

Re: NEAREST [message #225944 is a reply to message #225923] Thu, 22 March 2007 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with 
  data as (
    select b.*,
           rank() over (order by review_date-sysdate) rk
    from bill b
  )
select *
from data
where rk = 1
/

Regards
Michel
Re: NEAREST [message #225990 is a reply to message #225923] Thu, 22 March 2007 03:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you need to use ABS(review_Date-sysdate) - otherwise it wont
You can do it without analytic function too (if you're on 8i)
SELECT *
FROM   (SELECT *
        FROM   bill
        ORDER BY abs(review_date-sysdate))
WHERE  rownum = 1; 
Re: NEAREST [message #225992 is a reply to message #225990] Thu, 22 March 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Analytic function, especially rank, already existed in 8i.
There were introduced in 8.1.6.

But you're right you query is more efficient if there is no chance to get several rows with the same review_date.

And I also forgot the ABS. Embarassed

Regards
Michel


Re: NEAREST [message #226001 is a reply to message #225992] Thu, 22 March 2007 04:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True - You just couldn't use them in Pl/Sql - I remember.

Re: NEAREST [message #226080 is a reply to message #226001] Thu, 22 March 2007 08:11 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i forgot to inform u that the date is in varchar2(30)
can i should use date function which function i need to use
thanxs
Re: NEAREST [message #226083 is a reply to message #226080] Thu, 22 March 2007 08:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
oracle_coorgi wrote on Thu, 22 March 2007 14:11
hi
i forgot to inform u that the date is in varchar2(30)
can i should use date function which function i need to use
thanxs

That's a pity. One should NEVER store dates in a VARCHAR2 column. You can cast it by using
TO_DATE(yourcolumn,'<date_format>')


So, in your case that would be:
SELECT *
FROM   (SELECT *
        FROM   bill
        ORDER BY abs(TO_DATE(review_date,'<date_format>')-sysdate))
WHERE  rownum = 1; 


Now all you have to do is identify the date format.

MHE
Re: NEAREST [message #226255 is a reply to message #226083] Fri, 23 March 2007 01:43 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
HI
thanxs all it worked

Re: NEAREST [message #227529 is a reply to message #226255] Wed, 28 March 2007 08:36 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
HI
one more request the data in the column is stored as varchar2(30)
some of the data is
21-mar-2007
TBD_ENTERED
TBD
25-MAR-2007
I THINK BEcause of TBD_ENTERED the data is not returned
if i use it for other column it works
pls advice
thanxs

Re: NEAREST [message #227572 is a reply to message #227529] Wed, 28 March 2007 12:28 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
See how storing DATEs in VARCHAR2 columns is causing you headaches? They'll only get worse.
You'll need to filter on records that are valid DATEs. Search for IS_DATE or DATE IN VARCHAR2 COLUMN.
Re: NEAREST [message #229376 is a reply to message #227572] Sat, 07 April 2007 01:22 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
its long time unable to solve the issue
pls can any be little more albort/brief on this

i have stored the date data in varchar2(30) in column name expected_date and order_date in proj table
1. i want to get the nearest date of current (next) for expected_date
2. i want to get the nearest date of current (previous) for ordered_date
3.i want to get the All projects with order_date within 3 months of the current date

think today is 05April2007
i want for next =06April2007
and for previous i want 04April2007
and for 3 month 05July2007



Re: NEAREST [message #229381 is a reply to message #229376] Sat, 07 April 2007 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand why the previous answers do not fit your new but similar requirements.

Regards
Michel
Re: NEAREST [message #229382 is a reply to message #229381] Sat, 07 April 2007 02:07 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
CREATED THE FUNCTION
CREATE OR REPLACE FUNCTION is_date1(i_date IN varchar2) RETURN DATE IS
v_date DATE;
BEGIN
v_date := TO_DATE(i_date ,'dd/mm/rrrr');
RETURN v_date;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END is_date1;

create or replace FUNCTION is_dateORA(p_test date)
RETURN varchar2
IS
v_check_date DATE;
ret varchar2(30):='valid date';
BEGIN
BEGIN
v_check_date := TO_DATE(p_test);
EXCEPTION
WHEN OTHERS THEN
ret:='not a valid date';
RETURN
ret;
end;
RETURN ret;
END;

Function created.

SQL> select is_date1(sysdate) from dual;

IS_DATE1(
---------
07-APR-07

SQL> select is_dateORA(sysdate) from dual;

IS_DATEORA(SYSDATE)
------------------------------------------

valid date

HOW DO I RUN WITH MY ISSUE
PLS PROVIDE ME HELP
ITS URGENT

[Updated on: Sat, 07 April 2007 02:15]

Report message to a moderator

Re: NEAREST [message #229385 is a reply to message #229382] Sat, 07 April 2007 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, nothing is URGENT in a forum.
A forum is a cooperative action.
We do it for free.
We do it when we have time.
We do it when we want.
If you need an urgent help, call Oracle or another consultant and pay for it.

Now, TO_DATE on a date is WRONG. TO_DATE takes a string as parameter not a date.

Quote:
HOW DO I RUN WITH MY ISSUE

What is it?
You mixed several things and in the end I don't understand what you are searching for.

Regards
Michel
Re: NEAREST [message #229388 is a reply to message #229385] Sat, 07 April 2007 03:35 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
mike i dint mean to disturb u
as the situation was circuital here i just requested for an urgent
ok i do feel that is forum is the best i have gone through which responds to the issue as soon as possible
pls do the needful
thanxs



Re: NEAREST [message #229390 is a reply to message #229388] Sat, 07 April 2007 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I already gave a point (to_date(a_date) is wrong).
I can't help you more as I don't know what you want.
Maybe someone else better understand your post than I do.

Regards
Michel
Re: NEAREST [message #229391 is a reply to message #229388] Sat, 07 April 2007 04:54 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
hope i am clear now
the data is expected_date column and proj table is
TBD-APR-2007
TBD-APR-2010
25-MAR-2009
TBD-FEB-2009
TBD-FEB-2006
27-FEB-2009
TBD-MAR-2005
TBD-MAR-2008 so on
find the nearest date of the current date (next) say june date
find the nearest date of the current date (previous)say feb date

Re: NEAREST [message #229392 is a reply to message #229391] Sat, 07 April 2007 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is as clear as mud.
What is "TBD-APR-2007"? Is it data you have in your table?
Post a "select ... from proj ..."
Format it (query and result)
Post the result you want and the rules to get the result.
"Nearest" is nearest from what?
And what is nearest?

As Tom Kyte often said: "Explain this as if you explain it to your Mom".

In the end, explain why the previous answers don't fit your requirements. (You still don't answer this question, it seems to me this is the third time I ask you this).

Regards
Michel

[just a correction in bold line]

[Updated on: Sat, 07 April 2007 05:10]

Report message to a moderator

Re: NEAREST [message #229398 is a reply to message #229392] Sat, 07 April 2007 05:28 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
SQL> select FK_PROJ_ID,EXPECTED_DATE,REVIEWED_DATE,STATUS from PROJ_APPROVALS;

FK_PROJ_ID EXPECTED_DATE                  REVIEWED_DATE                  S
---------- ------------------------------ ------------------------------ -
     10020 31-FEB-2008
     10020 31-FEB-2008
     10020 31-FEB-2008
     10003 13-JUL-2007
     10015 TBD-APR-2010                   07-Apr-2007                    Y
     10015 27-FEB-2009                    13-Apr-2007                    Y
     10016 24-MAR-2011                                                   Y
     10016 25-MAR-2009                                                   N
     10016 23-FEB-2009                                                   N
     10017 TBD-FEB-2008                   05-Apr-2007                    Y
     10017 TBD-APR-2007                   03-Apr-2007                    N

FK_PROJ_ID EXPECTED_DATE                  REVIEWED_DATE                  S
---------- ------------------------------ ------------------------------ -
     10017                                03-Apr-2007                    N
     10018 TBD-FEB-2007
     10018 TBD-MAR-2008
     10018 TBD-MAR-2008
     10015 2-FEB-2010                     05-Apr-2007                    Y
     10019 TBD-FEB-2007
     10019 TBD-MAR-2008
     10019 TBD-MAR-2008

19 rows selected.

nearest means nearest to sysdate

[Updated on: Sat, 07 April 2007 05:49]

Report message to a moderator

Re: NEAREST [message #229399 is a reply to message #229398] Sat, 07 April 2007 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To format the post read How to format your posts.
Then the answer to your question was already given.

Regards
Michel
Re: NEAREST [message #229401 is a reply to message #229399] Sat, 07 April 2007 06:13 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
HI
i need 3month before and after
is this query correct

1. SELECT MIN(TO_DATE(expected_date,'DD-MON-YYYY')-90) ,TO_CHAR(MIN(TO_DATE(expected_date,'DD-MON-YYYY')),'D-MON-YYYY') MD
 FROM PROJ_APPROVALS WHERE EXPECTED_DATE NOT LIKE '%TB%' AND (TO_DATE(expected_date,'DD-MON-YYYY')+ 90) >0 
 GROUP BY FK_APPROVER_CAT_ID]
2.SELECT expected_date 
, TO_CHAR( TO_DATE( expected_date, 'DD-MON-YYYY ) + 1 ) dayafter 
, TO_CHAR( TO_DATE( expected_date, 'DD-MON-YYYY ) - 1 ) daybefore 
FROM PROJ_APPROVALS ; 
[U]how to select[/U]
 to select with "MIN( a_date )" and MAX( a_date )"). 
Re: NEAREST [message #229402 is a reply to message #229401] Sat, 07 April 2007 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
a) Use add_months function
b) Read again my first answer

Regards
Michel
Re: NEAREST [message #229403 is a reply to message #229402] Sat, 07 April 2007 06:30 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
i not able to get
can u pls send the query
hope u understand my pressure

thanxs
Re: NEAREST [message #229405 is a reply to message #229403] Sat, 07 April 2007 06:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Your first post was from over two weeks ago. You had plenty time to find a solution, so please stop whining about the urgency of your problem.
This forum is not meant to be a place where you can feed your requirements and retrieve a full blown query. We still expect you to do your own work. After all, YOU get paid for your work, we don't.
date [message #230895 is a reply to message #225923] Fri, 13 April 2007 04:22 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
select records which are > then three month from the sysdate(current date)

SELECT TO_CHAR(TO_DATE(tested_DATE, 'DD-MON-RR') ,'MON-DD-YYYY')"Year" FROM testing; 

sysdate + 90
thanxs
Re: date [message #230900 is a reply to message #230895] Fri, 13 April 2007 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
add_months
Don't you post the same question a couple of days ago?
I posted the same answer.

Regards
Michel
Re: date [message #230904 is a reply to message #230900] Fri, 13 April 2007 04:49 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
this is a diff post and diff query
find date [message #231982 is a reply to message #225923] Thu, 19 April 2007 00:25 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
iam getting correct records from the first query
1.select * from test where test_DATE < SYSDATE + 90 
i need to get the out put as above query using between from the second query
2.select * from test where sysdate-test_DATE between 0 and 91 

thanxs
Re: find date [message #231988 is a reply to message #231982] Thu, 19 April 2007 00:43 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi oracle_coorgi,


I don't think you will get the same answer from both query.
let say
test_DATE=4/19/2005

select * from test where test_DATE < SYSDATE + 90 


this query will return the record for test_DATE=4/19/2005
..
but second query will become like that ..

 select * from test where 730 between 0 and 91

this will not select the record which testdate is 4/19/2005

--Yash
Re: find date [message #231990 is a reply to message #231988] Thu, 19 April 2007 00:49 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
is there any way i can use between for this query
Re: find date [message #231993 is a reply to message #231990] Thu, 19 April 2007 00:56 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Try this


select * from test where (sysdate+90)-test_DATE between 0 and (sysdate+90)-test_DATE



--Yash
Re: find date [message #231994 is a reply to message #231993] Thu, 19 April 2007 01:05 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
great mishra it worked thanxs a lot


Re: find date [message #231997 is a reply to message #231982] Thu, 19 April 2007 01:11 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't say why you can't use add_months.
But after at least 3 threads on this topic in different forums I doubt you will ever answer this question.

Previous Topic: how can we call java code in oracle procedure
Next Topic: query
Goto Forum:
  


Current Time: Sat Dec 03 06:03:48 CST 2016

Total time taken to generate the page: 0.11996 seconds