Home » SQL & PL/SQL » SQL & PL/SQL » select date values from a table between two dates (merged) (Windows 2003 and Oracle 10g)
select date values from a table between two dates (merged) [message #443496] Tue, 16 February 2010 01:31 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: select date values from a table between two dates [message #443501 is a reply to message #443496] Tue, 16 February 2010 01:45 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
please post actule code and sql session output
Re: select date values from a table between two dates [message #443502 is a reply to message #443497] Tue, 16 February 2010 01:45 Go to previous messageGo to next message
ramoradba
Messages: 2454
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 Smile
Re: select date values from a table between two dates [message #443506 is a reply to message #443502] Tue, 16 February 2010 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good catch! Wink

Regards
Michel
Re: select date values from a table between two dates [message #443514 is a reply to message #443502] Tue, 16 February 2010 01:56 Go to previous messageGo to next message
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
Re: select date values from a table between two dates [message #443515 is a reply to message #443506] Tue, 16 February 2010 01:57 Go to previous messageGo to next message
ramoradba
Messages: 2454
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
Re: select date values from a table between two dates [message #443516 is a reply to message #443515] Tue, 16 February 2010 01:58 Go to previous message
ramoradba
Messages: 2454
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
Previous Topic: Find out Object name
Next Topic: double recursion
Goto Forum:
  


Current Time: Sun Sep 25 23:13:09 CDT 2016

Total time taken to generate the page: 0.13086 seconds