SQL Server 2005 - Getting Results for next week

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server 2005 - Getting Results for next week

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-08-11 : 05:45:59

If I have date column and I want to use this to filter to only return results with dates for this week (i.e the current week) what query do I need to run on the column

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 05:47:43

How is a week defined for you?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 05:49:12

[code]DECLARE @ThisWeek DATETIME

SET @ThisWeek = DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101')

SELECT *
FROM YourTable
WHERE DateCol >= @ThisWeek
AND DateCol < DATEADD(DAY, 7, @ThisWeek)[/code]

E 12°55'05.25"
N 56°04'39.16"

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-08-11 : 05:49:40

Heys,

Lets say everything between today Monday 10:49 and next monday 10:49

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 05:52:15

declare @weekstart datetime
select @weekstart=dateadd(wk,datediff(wk,0,GETDATE()),0)

select fields
from yourtable
where datefield>@weekstart
and datefield<dateadd(d,5,@weekstart)

if you want 5 day week else add 7

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 05:53:12

quote:
Originally posted by Velnias

Heys,

Lets say everything between today Monday 10:49 and next monday 10:49

then use
declare @weekstart datetime
select @weekstart=dateadd(wk,datediff(wk,0,GETDATE()),0)

select fields
from yourtable
where datefield>@weekstart
and datefield<dateadd(d,7,@weekstart)

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-08-11 : 06:00:20

Great thanks

Question how Would I set @weekstart automatically to current date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 06:04:06

SET @WeekStart = GETDATE()
SET @WeekStart = DATEADD(DAY, DATEDIFF(DAY, '19000101', getdate()), '19000101')

E 12°55'05.25"
N 56°04'39.16"

Velnias
Yak Posting Veteran

58 Posts

Posted - 2008-08-11 : 06:05:21

thanks, also could you explain the line below to me
(CONVERT(INT, tblResidential_Viewings.date, 103)

what does the 103 do.

Sorry for all the questions

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 06:06:15

It's converting the datetime value into an integer value, which denoted the number of days passed since January 1, 1900.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources