CODEWITHSUNDEEP
sqlparameterscasting
Ms Jen
Ms Jen

Reputation: 33

Value from @Parameter

Im trying to do this

SELECT     CAST(date AS datetime) + CAST(time AS datetime) AS NewDT, variable, value
FROM         batch
WHERE     (NewDT <= @BatchStartDate)

But i get the error "Invalid colum name NewDT" The problem is that date and time is in two colums in the database.

Upvotes: 0

Views: 58

Answers (2)

SWeko
SWeko

Reputation: 30912

(Also guessing that you use SQL Server)

You cannot use an alias in the where clause, so you can use the whole expression (as in Yaroslav's answer) or you can wrap the original select as a subquery, like this:

select * from 
(
   SELECT CAST(date AS datetime) + CAST(time AS datetime) AS NewDT, variable, value
   FROM batch
)
WHERE NewDT <= @BatchStartDate

Upvotes: 0

Yaroslav
Yaroslav

Reputation: 6544

Guessing you are using MS SQL Server. You can't reference an alias on the WHERE clause, you should use the full CAST(date AS datetime) + CAST(time AS datetime) so it would be:

SELECT CAST(date AS datetime) + CAST(time AS datetime) AS NewDT, variable, value
  FROM batch
 WHERE (CAST(date AS datetime) + CAST(time AS datetime) <= @BatchStartDate)

Upvotes: 2

Related Questions