[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Incorrect syntax near the keyword 'BETWEEN'

Posted on 2012-03-27
3
Medium Priority
?
1,203 Views
Last Modified: 2012-03-27
I could not figure out what is wrong with my syntax:

declare @StartDate DateTime,
@EndDate DateTime,
@BranchName varchar (20),
@ReportType varchar(50)
set @StartDate='02/1/2012'
set @EndDate='02/29/2012'

SELECT [Branch Name], [Stage Number],
[Not Signed Application Date],
[Fund Date],
[UW Approve Date]
FROM          tablename
WHERE        
 [Branch Name] in(@BranchName)AND ([Stage Number] >= 20)  AND
case
 WHEN @ReportType=a'
    THEN [Not Signed Application Date] BETWEEN @StartDate AND @EndDate
WHEN @ReportType='b'
  THEN [Fund Date] BETWEEN @StartDate AND @EndDate
WHEN @ReportType='c'
   THEN [UW Approve Date] BETWEEN @StartDate AND @EndDate
END


Thanks!
0
Comment
Question by:sharon2011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37771492
The THEN and ELSE results must be a single *value* (or an expression that ultimately yields a single value), NOT a keyword or other multi-word result.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37771501
Therefore, change it to this:


case
 WHEN @ReportType=a' AND [Not Signed Application Date] BETWEEN @StartDate AND @EndDate THEN 1
WHEN @ReportType='b' AND [Fund Date] BETWEEN @StartDate AND @EndDate THEN 1
WHEN @ReportType='c' AND [UW Approve Date] BETWEEN @StartDate AND @EndDate THEN 1
ELSE 0
END = 1
0
 

Author Closing Comment

by:sharon2011
ID: 37771583
It works. Thank you very very much.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question