Solved

SQL syntax error near '=' using CAST([Table].Column AS DATE

Posted on 2014-09-03
4
521 Views
Last Modified: 2014-09-03
Hello Experts;

The following query generates a "syntax error near '=' in the CAST([Transaction].Time AS Date) = @TransTime" function;

declare @TransTime as date = :TransTime
declare @TransTime2 as date = :TransTime2

SELECT
SUM(CASE WHEN [Transaction].CashierID = 4
THEN [Transaction].Total ELSE 0 END) AS Bill
FROM [Transaction]
WHERE [Transaction].Time
BETWEEN
CAST([Transaction].Time AS Date) = @TransTime
AND
CAST([Transaction].Time AS Date) = @TransTime2

The CAST AS Date works using a single date but adding the BETWEEN, AND functions for a second CAST AS Date function creates the error. I must be doing something wrong in attempting to query between two dates but I cannot figure why, any help would be greatly appreciated.

Thank you,
Visionetv
0
Comment
Question by:visionetv
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
Jan Louwerens earned 500 total points
ID: 40302327
The syntax is supposed to be:
BETWEEN <date1> AND <date2>

but it looks like you're doing:
BETWEEN <assignment_operation> AND <assignment_operation>
(Not to mention that these assignment operations are illegal, since the left sides are not assignable.)

Do you maybe just want?
BETWEEN @TransTime AND @TransTime2
0
 
LVL 11

Expert Comment

by:Murfur
ID: 40302334
BETWEEN
@TransTime
AND
@TransTime2
0
 
LVL 11

Expert Comment

by:Murfur
ID: 40302337
Apologies Jan, your answer! (There were no answers here when I first loaded the page...)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40302382
...
WHERE CAST([Transaction].Time AS date)
 BETWEEN @TransTime AND @TransTime2
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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 …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now