Week Function

1. Senior Member
Join Date
Dec 1969
Posts
490

## Week Function

Hi,<BR><BR>I&#039;m trying to select events for next week starting monday.<BR><BR>I have...<BR><BR>SELECT * FROM tblEvents WHERE e_date BETWEEN GETDATE()+7 AND GETDATE()+14<BR><BR>But that just returns 7 days worth of events, 7 days from today.<BR><BR>I can do...<BR>WHERE Month(e_date) = month(GETDATE())+1 <BR><BR>... to get next month... but can&#039;t do...<BR>WHERE WEEK(e_date) = WEEK(GETDATE())+1<BR><BR>SQL Server 2000 advises that WEEK is not a recognized function.<BR><BR>Can anyone please help?<BR><BR>Cheers,<BR><BR>Kes

2. Senior Member
Join Date
Dec 1969
Posts
196

## RE: Week Function

I dont know a lot about this, but I think you need to calculate the week number and use that to do your calculations as there isn&#039;t an actual WEEK function in SQL Server.<BR><BR>Something like this? <BR><BR>WHERE WEEK(e_date) = datepart(wk, GETDATE())+1

3. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Week Function

might also want to look at the DATEADD function

4. Senior Member
Join Date
Dec 1969
Posts
490

## Similar and works... ish

datepart(wk, e_date) = datepart(wk, GETDATE())+1<BR><BR>returns the following week starting from sunday i.e. the ISO standard.<BR><BR>What would be nice is if it could start from monday... everyone elses standard...

5. Senior Member
Join Date
Dec 1969
Posts
11,247

## @@DATEFIRST

&nbsp;<BR>Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.<BR><BR>Syntax<BR>@@DATEFIRST<BR><BR>Return Types<BR>tinyint<BR><BR>Remarks<BR>The U.S. English default is 7, Sunday.<BR><BR>Examples<BR>This example sets the first day of the week to 5 (Friday), and assumes the current day to be Saturday. The SELECT statement returns the DATEFIRST value and the number of the current day of the week.<BR><BR>SET DATEFIRST 1<BR><BR> <BR><BR><BR> <BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: @@DATEFIRST

Great answer WK!<BR><BR><BR><BR><BR>(I corrected you a few times the past few days, I felt I needed to make a compliment as well ;-) )

7. Senior Member
Join Date
Dec 1969
Posts
11,247

## Blush factor high! <nt>

.

8. Senior Member
Join Date
Dec 1969
Posts
490

## Cheers

SET DATEFIRST 1 SELECT * FROM tblEvents WHERE DATEPART(wk, e_date) = DATEPART(wk, GETDATE())+1<BR><BR>...does the job nicely.<BR><BR>Ta

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•