• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1228
  • Last Modified:

Incorrect syntax near the keyword 'BETWEEN'

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
sharon2011
Asked:
sharon2011
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
sharon2011Author Commented:
It works. Thank you very very much.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now