September 15, 2009 at 12:36 pm
DECLARE @MonthStartDate DATETIME
DECLARE @MonthEndDate DATETIME
DECLARE @MonthToDate DATETIME
SET @MonthStartDate ='5/1/2009'
SET @MonthEndDate ='5/31/2009'
SET @MonthToDate BETWEEN @MonthEndDate AND @MonthStartDate
SELECT @MonthToDate
produces error
Incorrect syntax near the keyword 'BETWEEN'
Please Help .
September 15, 2009 at 12:40 pm
What are you trying to accomplish?
September 15, 2009 at 12:41 pm
"Between" doesn't do what you want it to do. I'm not sure what you want it to do, but it isn't used for assigning values. It's used to check if one value is between two other values.
What are you trying to accomplish? Are you looking for the middle date between two other dates?
If so, try something like this:
declare @FirstDate datetime, @LastDate datetime, @MiddleDate datetime;
select @FirstDate = '9/1/09', @LastDate = '9/30/09';
select @MiddleDate = dateadd(day, .5 * datediff(day, @FirstDate, @LastDate), @FirstDate);
select @MiddleDate;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2009 at 12:54 pm
DECLARE @MonthStartDate DATETIME
DECLARE @MonthEndDate DATETIME
DECLARE @MonthToDate DATETIME
SET @MonthStartDate ='5/1/2009'
SET @MonthEndDate ='5/31/2009'
SET @MonthToDate BETWEEN @MonthEndDate AND @MonthStartDate
SELECT @MonthToDate
I am trying to assign the Dates between '5/1/2009' AND '5/31/2009' to variable @MonthToDate
September 15, 2009 at 12:58 pm
You're trying to assign all of the dates between those? You can't assign multiple values to a single variable.
What is this part of? What will you use those dates for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2009 at 1:23 pm
I am trying to calculate revenue between the startdate and enddates.
DECLARE @MonthStartDate DATETIME
DECLARE @MonthEndDate DATETIME
DECLARE @MonthToDate DATETIME
DECLARE @TodaysDate DATETIME
SET @TodaysDate = '5/29/2009'
IF (@TodaysDate BETWEEN '3/27/2009' AND '4/23/2009')
BEGIN
SET @MonthStartDate = '3/27/2009'
SET @MonthEndDate = GETDATE()
END
ELSE IF (@TodaysDate BETWEEN '4/24/2009' AND '5/21/2009')
BEGIN
SET @MonthStartDate = '4/24/2009'
SET @MonthEndDate = GETDATE()
END
ELSE IF (@TodaysDate BETWEEN '5/22/2009' AND '6/25/2009')
BEGIN
SET @MonthStartDate = '5/22/2009'
SET @MonthEndDate = GETDATE()
END
ELSE IF (@TodaysDate BETWEEN '6/26/2009' AND '7/23/2009')
BEGIN
SET @MonthStartDate = '6/26/2009'
SET @MonthEndDate = GETDATE()
END
ELSE IF (@TodaysDate BETWEEN '7/24/2009' AND '8/20/2009')
BEGIN
SET @MonthStartDate = '7/24/2009'
SET @MonthEndDate = GETDATE()
END
ELSE IF (@TodaysDate BETWEEN '8/21/2009' AND '9/24/2009')
BEGIN
SET @MonthStartDate = '8/21/2009'
SET @MonthEndDate = GETDATE()
END
ELSE IF (@TodaysDate BETWEEN '9/25/2009' AND '10/22/2009')
BEGIN
SET @MonthStartDate = '9/25/2009'
SET @MonthEndDate = GETDATE()
END
ELSE IF (@TodaysDate BETWEEN '10/23/2009' AND '11/19/2009')
BEGIN
SET @MonthStartDate = '10/23/2009'
SET @MonthEndDate = GETDATE()
END
SET @MonthToDate BETWEEN @MonthEndDate AND @MonthStartDate
SELECT @MonthToDate
Thanks.
September 15, 2009 at 1:34 pm
What do you then plan to do with the variable? Does it go into the Where clause of a select statement?
If so, why not just use the start and end dates that you already have assigned?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2009 at 1:36 pm
As GSquared said:
you can use the variables you already have
select sum(revenue) from table1
where revenueDate between @dMonthStartDate and @MonthEndDate
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply