Solved

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

Posted on 2014-09-03
4
536 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

809 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