Geeks With Blogs
Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
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
Posted on Friday, January 4, 2008 3:25 AM Oracle , Dates | Back to top


Comments on this post: Get weeks from oracle

# re: Get weeks from oracle
Requesting Gravatar...
Very helpful post, thanks!
Left by canvas art on Dec 06, 2011 5:22 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net