CODEWITHSUNDEEP
sql-serversql-server-2008
thormayer
thormayer

Reputation: 1070

DateDiff with WHERE clause NOT NULL

Im trying to get the time difference between two values in a table and eliminate all NULLs.

SELECT NumberOfMinutes =
   DATEDIFF(millisecond, tbl_hugo_game.time_start, tbl_hugo_game.time_end)
FROM tbl_hugo_game 
WHERE numberofminutes <> NULL 
ORDER BY numberofminutes ASC

without the WHERE-clause it is working fine, otherwise :

Msg 207, Level 16, State 1, Line 3 Invalid column name 'numberofminutes'.

What's wrong?

Upvotes: 0

Views: 2055

Answers (3)

Atheer Mostafa
Atheer Mostafa

Reputation: 735

Where Clause can't access column aliases. It's better and simpler for your case to rewrite the query (without using of subselect) simply as:

SELECT NumberOfMinutes =
DATEDIFF(millisecond, tbl_hugo_game.time_start, tbl_hugo_game.time_end)
FROM tbl_hugo_game 
WHERE tbl_hugo_game.time_start  IS NOT NULL AND tbl_hugo_game.time_end IS NOT NULL
ORDER BY numberofminutes ASC

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

The logical processing order of this SELECT statement is

a) FROM tbl_hugo_game

b) WHERE numberofminutes <> NULL

c) 

SELECT
DATEDIFF(millisecond ,tbl_hugo_game.time_start,tbl_hugo_game.time_end) AS NumberOfMinutes

d) ORDER BY numberofminutes ASC

Thus, the step c) (the SELECT clause with DATEDIFF ... AS NumberOfMinutes computed field) is processed after step b) (the WHERE clause) and before step d) (the ORDER BY clause). This is the reason why the computed field from step c) (DATEDIFF ... AS NumberOfMinutes) can't be used in WHERE clause (step b) but can be used in the ORDER BY clause (step d).

References: SELECT (Transact-SQL) # Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Upvotes: 0

SWeko
SWeko

Reputation: 30902

You cannot use column aliases in a where clause.

You can, however, put the query with the alias as a source for another query, like this:

select * from (
  select DATEDIFF(...) AS NumberOfMinutes 
  FROM tbl_hugo_game) source
where NumberOfMinutes is not null 
order by NumberOfMinutes asc

Upvotes: 4

Related Questions