Solved

List of date in previous 4 months - oracle 10 sql

Posted on 2010-11-29
3
615 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle DBLINKS From 11g to 8i 3 32
Oracle Query - Return results based on minimum value 8 32
convert null in sql server 12 33
Query group by data in SQL Server - cursor? 3 31
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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