OraFAQ Forum: SQL & PL/SQL » select date values from a table between two dates (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » select date values from a table between two dates (merged) (Windows 2003 and Oracle 10g)

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

select date values from a table between two dates (merged) [message #443496]

Tue, 16 February 2010 01:31

aijaz786
Messages: 91
Registered: February 2010

Member

Oracle 10g

In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.

select update_date from table1
where update_date between sysdate and sysdate-90

The above query retrun no data even data is there in the table for this range. Please advise what to do?

Update_date

11-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
07-FEB-10
04-FEB-10
04-FEB-10
04-FEB-10
04-FEB-10
03-FEB-10
03-FEB-10

[Edit MC: Remove useless hundred lines of dates]

[Updated on: Tue, 16 February 2010 01:48] by Moderator

Report message to a moderator

Re: select date values from a table between two dates [message #443497 is a reply to message #443496]

Tue, 16 February 2010 01:35

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Are you sure that "11-FEB-10" is year 2010 and not year 0010?
Use a date format with YYYY

Regards
Michel

Report message to a moderator

Re: select date values from a table between two dates [message #443501 is a reply to message #443496]

Tue, 16 February 2010 01:45

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

please post actule code and sql session output

Report message to a moderator

Re: select date values from a table between two dates [message #443502 is a reply to message #443497]

Tue, 16 February 2010 01:45

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

SQL> select * from p2;

DATA_DATE
---------
16-FEB-10
15-FEB-10
06-FEB-10
17-JAN-10
18-NOV-09
08-DEC-09
28-NOV-09
23-NOV-09
21-NOV-09

9 rows selected.

SQL>  select * from p2
  2  where data_date between sysdate-90 and sysdate;

DATA_DATE
---------
16-FEB-10
15-FEB-10
06-FEB-10
17-JAN-10
08-DEC-09
28-NOV-09
23-NOV-09
21-NOV-09

8 rows selected.

SQL>  select data_date from p2
  2   where data_date between  sysdate and sysdate-90;

no rows selected

SQL>

sriram

Report message to a moderator

Re: select date values from a table between two dates [message #443506 is a reply to message #443502]

Tue, 16 February 2010 01:47

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Good catch! Wink

Regards
Michel

Report message to a moderator

Re: select date values from a table between two dates [message #443514 is a reply to message #443502]

Tue, 16 February 2010 01:56

aijaz786
Messages: 91
Registered: February 2010

Member

It means that

between sysdate and sysdate-90 will not work. I must use sysdate-90 and sysdate.

It is strange. In Access, If I use date() and date()-90 works fine. Please give detail.

Thanks to all for qucik reply.

Regards.

Aijaz

Report message to a moderator

Re: select date values from a table between two dates [message #443515 is a reply to message #443506]

Tue, 16 February 2010 01:57

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

i guess it would be better if you Append Your "Pre Auto message"

Read the forum guide lines as a reply to the OP.
As he did n`t followed it i posted the same But you deleted that.

Hope I am clear in this.
Sriram

Report message to a moderator

Re: select date values from a table between two dates [message #443516 is a reply to message #443515]

Tue, 16 February 2010 01:58

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

This is Oracle Not Access.
There is a lot of difference B/w these.
Read the Oracle documents first.
Good luck

sriram

Report message to a moderator

Previous Topic:	Find out Object name
Next Topic:	double recursion

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:38:57 CST 2024