File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql stmt to get dates

 
meera rao
Ranch Hand
Posts: 67
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to write a sql statement to get the last seven dates in a database given a date.

example: I have the foll dates in the d/b

7/11/2005
7/12/2005
7/13/2005
7/16/2005
7/18/2005
7/22/2005
7/23/2005

if i/p is 7/11/2005, i should get
7/11/2005
7/12/2005
7/13/2005
7/16/2005
7/18/2005
7/22/2005

with the sql stmt that i wrote i am getting
7/11/2005
7/12/2005
7/13/2005
7/16/2005

select reqDate from tbl where reqDate between to_date('7/11/2005','mm/dd/yyyy')and to_date('7/11/2005','mm/dd/yyyy')+6
 
Makarand Parab
Ranch Hand
Posts: 121
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For ur problem
select reqDate from tbl where reqDate between to_date('7/11/2005','mm/dd/yyyy')and to_date('7/11/2005','mm/dd/yyyy')+6

try this out
select date_n, rownum from
(select distinct (to_date(tab.reqDate)) as date_n from tbl tab
order by to_date(tab.reqDate) asc ) where rownum <= 6
 
meera rao
Ranch Hand
Posts: 67
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much
 
Makarand Parab
Ranch Hand
Posts: 121
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Meera
Did it work for your requirement?. Let me know.

Regards
Makarand Parab
 
meera rao
Ranch Hand
Posts: 67
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually it works partially. If I want to get the next 6 rows from a particular date, how would i do it.

say i have all these dates

7/11/2005
7/12/2005
7/13/2005
7/16/2005
7/18/2005
7/21/2005
7/24/2005
7/25/2005
7/26/2005
7/27/2005
7/28/2005
7/29/2005

I want all the dates from 7/21/2005 to 6 days later
 
Makarand Parab
Ranch Hand
Posts: 121
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check this out if it works for input 7/21/2005

select date_n, rownum from
(select distinct (to_date(tab.reqDate)) as date_n from tbl tab
where to_date(tab.reqDate) > to_date('07/21/2005','DD/MM/YYYY')
order by to_date(tab.reqDate) desc)
where rownum <= 6

Check this out and let me know if this query works.

Regards
Makarand Parab
 
Makarand Parab
Ranch Hand
Posts: 121
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry
It will be like this

Check this out if it works for input 7/21/2005

select date_n, rownum from
(select distinct (to_date(tab.reqDate)) as date_n from tbl tab
where to_date(tab.reqDate) <= to_date('21/07/2005','DD/MM/YYYY')
order by to_date(tab.reqDate) desc)
where rownum <= 6

Please check the way i have given the input, it is not '7/21/2005' but
'21/07/2005' as we want 'DD/MM/YYYY' format

Check this out and let me know if this query works.

Regards
Makarand Parab
 
Makarand Parab
Ranch Hand
Posts: 121
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry i messed up a bit again, i gave you the query for previous dates
Here is the final one i hope

select date_n, rownum from
(select distinct (to_date(tab.reqDate)) as date_n from tbl tab
where to_date(tab.reqDate) >= to_date('21/07/2005','DD/MM/YYYY')
order by to_date(tab.reqDate) desc)
where rownum <= 6

Regards
Makarand Parab
 
meera rao
Ranch Hand
Posts: 67
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it works perfectly , thank you so much
 
Makarand Parab
Ranch Hand
Posts: 121
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Meera
My core expertise is Java, J2EE and i am learning SQL. So if u have any problem with SQL let me know. I would surely give a try as it would be a live project for me .
Good to know that it worked fine. Enjoy

Regards
Makarand Parab
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic