?
Solved

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

Posted on 2014-09-03
4
Medium Priority
?
629 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
[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
4 Comments
 
LVL 5

Accepted Solution

by:
Jan Louwerens earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

718 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