SQL Query to return Week Start Date from a given midweek date?

[thebatfink]

Hi! Bit of a tongue twister topic title but heres what I want to achieve..

I have an existing datetime field named "PlanDay". I want to create a query which will select this date and also a calculated field [PlanWeek] which gives me the Week commencing date (the date of the Monday in the same week) of the selected existing date.

For example here are values in the [PlanDay] field

[FONT=Courier New][PlanDay][/FONT]
[FONT=Courier New]04/02/2013[/FONT]
[FONT=Courier New]05/02/2013[/FONT]
[FONT=Courier New]14/02/2013[/FONT]
[FONT=Courier New]24/02/2013[/FONT]

I would like my query to return..

[FONT=Courier New][PlanDay]     [PlanWeek][/FONT]
[FONT=Courier New]04/02/2013    04/02/2013[/FONT]
[FONT=Courier New]05/02/2013    04/02/2013[/FONT]
[FONT=Courier New]14/02/2013    11/02/2013[/FONT]
[FONT=Courier New]24/02/2013    18/02/2013[/FONT]

SELECT [PlanDay], SOMEFUNCTION(PlanDay) AS [PlanWeek]
FROM [PlanData]

Is this possible? Any help would be much appreciated! Thanks!

 

[gemma-the-husky]

there will be a slightly easier way (ie a single statement) but this fragment will do the job in a few clock cycles.


while weekday(indate)<>vbmonday
indate=indate-1
wend

indate will now be set to monday.

 

[bob fitz]

Perhaps the attached db will help.