Data Range - SQL Server Forums

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

Data Range

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-04 : 23:28:16

Hi,

I have a where statement that covers the date range May 4 - May 12. However, it moves or rolls out every week. Is there a way to make it moves on May 13.

Here's my statement :

WHERE (Reviewdate >= DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))) AND (Reviewdate <= DATEADD(dd, 13, DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-04 : 23:48:15

What do you mean by "moves or rolls out"?

What is the data type of Reviewdate?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-04 : 23:50:01

I want the date to be moved every other week and not every week. Reviewdate - Dates.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-04 : 23:57:13

I still don't understand what you mean. Please remember that we don't know your environment, so you need to be very explicit with what you want. It is always helpful to illustrate your problem by providing sample data that shows what you mean.

You didn't answer what data type Reviewdate is. Is it datetime or varchar?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-05-04 : 23:57:58

It's datetime. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 08:44:30

May 4 to May 12 is 9 days...

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 08:46:42

quote:
Originally posted by cutiebo2t

I have a where statement that covers the date range May 4 - May 12.

WHERE (Reviewdate >= DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))) AND (Reviewdate <= DATEADD(dd, 13, DATEADD(dd, - (1 * DATEPART(dw, GETDATE())) + 1, CONVERT(datetime, CONVERT(nvarchar(10), GETDATE(), 101)))))

This will give you May 4 to May 17. Not May 4 to May 12.

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-05 : 09:35:16

Set variables with your formulas so that you can review that your formulas are working BEFORE you incorporate them into your SQL statement. It is nearly impossible to troubleshoot your code when you have long complicated formulas embedded in your WHERE clause where you never conclusively see the results of those calculations.

i.e., simply set a @StartDate and @EndDate variables BEFORE your big SELECT, and reference those variables in your WHERE clause. This concept is a crucial, fundamental aspect of programming to understand that will make your life a hundred times easier.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-05 : 13:59:14

Are you trying to calculate a start and end date for a two week range that doesn;t change for two weeks?

This is not fully tested (and I'm sure there are boundry errors), but it might give you an idea of how to proceed (assuming my assuption is correct)

DECLARE @Today DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @Today = CURRENT_TIMESTAMP
SELECT DATEPART(WEEK, @Today)

SET @StartDate = CASE 
	WHEN DATEPART(WEEK, @Today) % 2 = 0 
		THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @Today), 0) - 1
	ELSE
		 DATEADD(WEEK, DATEDIFF(WEEK, -1, @Today), 0) - 1
	END
SET @EndDate =  DATEADD(WEEK, 2, @StartDate) - 1

SELECT @Today, @StartDate, @EndDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources