Forums Register Login

sql stmt to get dates

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
Thank you very much
+Pie Number of slices to send: Send
Hi Meera
Did it work for your requirement?. Let me know.

Regards
Makarand Parab
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
it works perfectly , thank you so much
+Pie Number of slices to send: Send
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
No thanks. We have all the government we need. This tiny ad would like you to leave now:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1358 times.
Similar Threads
Having trouble using Dates in SQL
Pass a resultset object from a servlet to jsp
java.sql.Timestamp to Oracle date
sql query problem
Date comparision in SQL from timestamp
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 05:24:25.