Solved

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

Posted on 2014-09-03
4
544 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:Scott Pletcher
ID: 40302382
...
WHERE CAST([Transaction].Time AS date)
 BETWEEN @TransTime AND @TransTime2
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

827 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