Get date for start of week

  • Thread starter Thread starter Duncs
  • Start date Start date
D

Duncs

I have the following query, which gives me the week number for a date:

SELECT DatePart("ww",[Date_Started],2,1) AS [Week Worked],
tblActionDetails.Worked_Status, Count(tblActionDetails.Worked_Status)
AS CountOfEntries
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
GROUP BY DatePart("ww",[Date_Started],2,1),
tblActionDetails.Worked_Status, tblStatus.StatusID
HAVING (((tblStatus.StatusID)<>1));

Date_Started is defined as a date field, and this gives me the week
number in the year that the date falls on. However, what I need to do
now is show the date for the start of that week, based on a Monday
week start. So, for example, week 46 should show a week commencing
date of 09/11/09, week 47 should show a w/c date of 16/11/09 etc.

I'm not sure how this would be achieved, but I'm sure it's got
something to do with the week number and adding the number of days
passed to it...or something like that ;).

Many TIA

Duncs
 
DateAdd("d",2-Weekday([Date_Started]),[Date_Started])

or

DateAdd("D",1-Weekday([Date_Started],2),[Date_Started])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Cheers John.

Working now.

Duncs

DateAdd("d",2-Weekday([Date_Started]),[Date_Started])

or

DateAdd("D",1-Weekday([Date_Started],2),[Date_Started])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


I have the following query, which gives me the week number for a date:
Click to expand...
SELECT DatePart("ww",[Date_Started],2,1) AS [Week Worked],
tblActionDetails.Worked_Status, Count(tblActionDetails.Worked_Status)
AS CountOfEntries
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
GROUP BY DatePart("ww",[Date_Started],2,1),
tblActionDetails.Worked_Status, tblStatus.StatusID
HAVING (((tblStatus.StatusID)<>1));
Click to expand...
Date_Started is defined as a date field, and this gives me the week
number in the year that the date falls on.  However, what I need to do
now is show the date for the start of that week, based on a Monday
week start.  So, for example, week 46 should show a week commencing
date of 09/11/09, week 47 should show a w/c date of 16/11/09 etc.
Click to expand...
I'm not sure how this would be achieved, but I'm sure it's got
something to do with the week number and adding the number of days
passed to it...or something like that ;).
Click to expand...
Many TIA
Click to expand...
Duncs- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top