Solved

Find the date of this week's Sunday

Posted on 2008-10-16
2
427 Views
Last Modified: 2013-11-27
I am trying to find the date of this week's Sunday.  I know this is the first day of the week but i dont know how to find that date programmitcally.   So for instance.. today is thursday 9/16/2008.  I want this field to show sunday october 9/12/2008 till next week when i would want it to show 9/19/2008
0
Comment
Question by:brandonjel
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 22735512
you  can use a function to do that, place this codes in a regular module

Function GetSundayDate(CurrentDate) As Date
   If VarType(CurrentDate) <> 7 Then

        GetSundayDate = Null
   Else

    Select Case Weekday(CurrentDate)
        Case 1
            GetSundayDate = CurrentDate
        Case Else
            GetSundayDate = DateAdd("d", -Weekday(CurrentDate) + 1, CurrentDate)
    End Select
   End If
   
End Function
1
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 22735755
Hello brandonjel,

No need for a UDF; native functions can get it readily.

SELECT SomeDate, DateValue(Format(SomeDate, "d mmm yyyy")) - Weekday(SomeDate) + 1 AS SundayDate
FROM SomeTable

Regards,

Patrick
1

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

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