Solved

List of date in previous 4 months - oracle 10 sql

Posted on 2010-11-29
3
613 Views
Last Modified: 2012-05-10
I need a query to list all dates in the last 6 months.
for example eg. we're currently in November,  so if i ran the query today it would list dates back to May. The first date always needs to be a friday so in this instance it will return the 1st Friday before the 1st of May (or return the 1st of may if its a friday), the last day would be the last friday before today (or today if today was friday). I hope this makes sense - i'll include some sample data which will hopefully make my requirments clearer.
sample-dates.xls
0
Comment
Question by:tonMachine100
  • 2
3 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 34232446
try this...

select next_day(add_months(trunc(sysdate,'mm'),-6)-7,'Friday') + level - 1 d from dual
connect by next_day(add_months(trunc(sysdate,'mm'),-6)-7,'Friday') + level <= sysdate
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34232473
oops, forgot the end point condition

change

+ level <= sysdate

to

+ level-1 <= next_day(sysdate-7,'Friday')
0
 

Author Closing Comment

by:tonMachine100
ID: 34237787
Thanks- thats spot on
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now