aspose file tools*
The moose likes JDBC and the fly likes sql stmt to get dates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql stmt to get dates" Watch "sql stmt to get dates" New topic
Author

sql stmt to get dates

meera rao
Ranch Hand

Joined: Jun 30, 2005
Posts: 67
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

Joined: Dec 10, 2004
Posts: 121
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

Joined: Jun 30, 2005
Posts: 67
Thank you very much
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
Hi Meera
Did it work for your requirement?. Let me know.

Regards
Makarand Parab
meera rao
Ranch Hand

Joined: Jun 30, 2005
Posts: 67
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

Joined: Dec 10, 2004
Posts: 121
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

Joined: Dec 10, 2004
Posts: 121
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

Joined: Dec 10, 2004
Posts: 121
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

Joined: Jun 30, 2005
Posts: 67
it works perfectly , thank you so much
Makarand Parab
Ranch Hand

Joined: Dec 10, 2004
Posts: 121
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: sql stmt to get dates