[dba-SQLServer] Current Week in SQL

Hi Stuart
Brilliant - works wonderfully well
Yes the dates and days are Aussie.
IE format will be dd/mm/yyyy and using Monday as the start of the (working) week


Ended up Using

declare @date datetime
set DATEFIRST  1  /* Set Monday as first day of the week */
set @date = GetDate() 
select convert(nvarchar(12), at date + 1 - datepart(dw, at date),6) as
'MondayThisWeek',  convert(nvarchar(12), at date + 7 - datepart(dw, at date),6) as
'SundayThisWeek'

Again many thanks

Darren
------------------
T: 0424 696 433

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Monday, 19 June 2006 2:04 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Current Week in SQL



On 19 Jun 2006 at 13:03, Darren DICK wrote:

> Hi all
> 
> Is there an easy way to determine the date of the Monday in the
> current week - Also the same for the Sunday of the current week?
> 
> E.g. - Assume today is Wednesday the 3rd April
> 
> I need help to determine that Monday is the 1st April 2006 and Sunday
> would be the 7th April
> 
> Just need to work our the Monday date and Sunday date of any week we
> are in
> 

Does your week start on Monday?  There are different conventions for this.

Assuming this is so, the following function will get Monday. Slight
modifications will get you 
the Sunday(last day of the week = 7) and by plugging other dates in place of
GetDate(), you 
can do it for any week.

declare @date datetime
set DATEFIRST  1  /* Set Monday as first day of the week */
set @date = GetDate() 
select convert(nvarchar(12), at date + 1 - datepart(dw, at date),6)

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com