1) Last 180 days of Monday Starts:
with
weeks as (
select
trunc(sysdate - 180, 'DAY') + 1 - 7 + (P.pivot*7) monday_start
from dual D, (select rownum pivot from all_objects
where rownum <
((((sysdate - (sysdate - 180)) + 1 ) / 7)) + 2) P
order by monday_start
)
select * from weeks
2) Weeks for specified data range
Public Function Select_Weeks( _
ByVal Start_DateString As String, _
ByVal End_DateString As String, _
ByRef sSql As String) As DataSet
Dim returnDs As New DataSet
Try
'select
'trunc(to_date('02/2/1980','MM/DD/YYYY'), 'DAY') + 1 - 7 + (P.pivot*7) last_monday,
'trunc(to_date('02/2/1980','MM/DD/YYYY'), 'DAY') + 7 - 7 + (P.pivot*7) next_sunday
'from dual D, (select rownum pivot from all_objects
'where rownum <
'((((to_date('02/16/1980','MM/DD/YYYY') - to_date('02/2/1980','MM/DD/YYYY')) + 1 ) / 7
')) + 2) P
' LAST_MONDAY NEXT_SUNDAY
'---------------------- ----------------------
' 28-Jan-1980 3-Feb-1980
' 4-Feb-1980 10-Feb-1980
' 11-Feb-1980 17-Feb-1980
' 18-Feb-1980 24-Feb-1980
sSql = "select" + vbCrLf _
+ " trunc(to_date('" + Start_DateString + "','MM/DD/YYYY'), 'DAY') + 1 - 7 + (P.pivot*7) last_monday," + vbCrLf _
+ " trunc(to_date('" + Start_DateString + "','MM/DD/YYYY'), 'DAY') + 7 - 7 + (P.pivot*7) next_sunday" + vbCrLf _
+ " from dual D, (select rownum pivot from all_objects" + vbCrLf _
+ " where rownum < " _
+ " ((((to_date('" + End_DateString + "','MM/DD/YYYY') - to_date('" + Start_DateString + "','MM/DD/YYYY')) + 1 ) / 7" + vbCrLf _
+ " )) + 2) P" + vbCrLf
returnDs = Me.GetDataset(sSql)
Catch ex As Exception
Throw ex
Finally
CleanUp(_objCommand)
End Try
Return returnDs
End Function