OraFAQ Forum: SQL & PL/SQL » query for date comparison

Home » SQL & PL/SQL » SQL & PL/SQL » query for date comparison

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query for date comparison [message #21540]

Sun, 11 August 2002 21:09

saritha
Messages: 15
Registered: October 2001

Junior Member

how can i retrieve data from a table posted between two dates?.
eg-select * from matrimonials m join person p on m.perid=p.perid where date between '8/1/2002' and '8/12/2002'

this query is not working

date variable declared as datetime.

also how can i do that when date variable declared as varchar.

Report message to a moderator

Re: query for date comparison [message #21546 is a reply to message #21540]

Mon, 12 August 2002 05:43

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

just change the syntax, it should work fine

local@ >select a.ename,b.loc from emp a , dept b
  2  where hiredate between '17-DEC-80' and ' 22-FEB-81';

ENAME      LOC
---------- -------------
SMITH      NEW YORK
ALLEN      NEW YORK
WARD       NEW YORK
SMITH      DALLAS
ALLEN      DALLAS
WARD       DALLAS
SMITH      CHICAGO
ALLEN      CHICAGO
WARD       CHICAGO
SMITH      BOSTON
ALLEN      BOSTON
WARD       BOSTON

Report message to a moderator

Re: query for date comparison [message #21552 is a reply to message #21546]

Mon, 12 August 2002 19:55

saritha
Messages: 15
Registered: October 2001

Junior Member

this doesn't work alright when i declare date as a varchar.!!

Report message to a moderator

Re: query for date comparison [message #21554 is a reply to message #21546]

Tue, 13 August 2002 04:43

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

local@ >desc date_test;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 D                                                              VARCHAR2(30)

local@ >select to_date(d,'dd-mon-yy') from date_test;

TO_DATE(D
---------
13-AUG-02
06-AUG-02

local@ >ed
Wrote file afiedt.buf

  1* select sysdate - to_date(d,'dd-mon-yy') from date_test
local@ >/

SYSDATE-TO_DATE(D,'DD-MON-YY')
------------------------------
                    .351990741
                    7.35199074

Report message to a moderator

Previous Topic:	Passing a string list to a procedure
Next Topic:	Names with single quotation marks

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:31:52 CDT 2024